Entry
Database: SQL: Simple: How create stored procedure in Microsoft SQL server, using SQL?
Jan 2nd, 2004 00:14
Knud van Eeden,
----------------------------------------------------------------------
--- Knud van Eeden --- 01 January 2004 - 01:48 am --------------------
Database: SQL: Simple: How create stored procedure in Microsoft SQL
server, using SQL?
---
Use the command
CREATE PROC <your stored procedure name>
@<your parameter name1> <your parameter datatype1> = <your parameter
initial value1>,
@<your parameter name2> <your parameter datatype2> = <your parameter
initial value2>,
@<your parameter name3> <your parameter datatype3> = <your parameter
initial value3>,
...
@<your parameter namelast> <your parameter datatypelast> = <your
parameter initial valuelast>
AS
<here come your SQL statements>
---
e.g.
CREATE PROC mystoredprocedurename1
AS
SELECT * FROM yourtable1
---
e.g.
CREATE PROC mystoredprocedurename3
@parametername1 NVARCHAR( 255 ) = '',
@parametername2 NVARCHAR( 255 ) = '',
@parametername3 NVARCHAR( 255 ) = '',
@parametername4 NVARCHAR( 255 ) = ''
AS
SELECT yourcolumnname1 FROM yourtable1 WHERE yourcolumnname1 =
@parametername4
---
---
Note:
For the moment I assume you can only put the
parameter on the right side of the '=' sign.
And not on the left side, or stand alone.
This seems to be the case, as far as I can
tell now.
So in order to let your stored procedure
be created completely variable, a
possible solution would be to generate
the text it from e.g. Visual Basic.NET, and
then just use string concatenation to
insert the values of your liking.
e.g.
create an sql statement in Visual Basic.NET
like the following:
"CREATE PROC mystoredprocedurename1" & " AS SELECT " & yourstring & "
FROM yourtable1"
then execute this statement.
---
---
Note:
You do not have to initialize your parameters,
e.g. when they get their value via the EXEC.
---
---
Note:
If you do not pass that parameter into the
stored procedure, it will take the initial
value.
---
---
Note:
The stored procedure should be on the top of
your SQL program
(and after the 'USE database' statement)
---
---
Note:
Put a '@' in front of your parameternames, when creating
your SQL statements.
---
---
Note:
Test this e.g. with Microsoft SQL server Query Analyzer.
---
---
Note:
You see that the structure is similar to procedures
and functions, or methods in other computer languages.
---
---
Note: to indicate which database to use, put this command
first:
USE <your database name>
GO
---
e.g.
USE Northwind
GO
CREATE PROC mystoredprocedurename1
@parametername1 NVARCHAR( 255 ),
@parametername2 NVARCHAR( 255 )
AS
SELECT * FROM Employees WHERE Firstname = @parametername2
GO
---
---
e.g.
So all together:
USE Northwind
GO
IF EXISTS ( SELECT name FROM sysobjects
WHERE name = 'mystoredprocedurename1' AND type = 'P')
DROP PROCEDURE mystoredprocedurename1
GO
CREATE PROC mystoredprocedurename1
@parametername1 NVARCHAR( 255 )
AS
SELECT * FROM Employees WHERE Firstname LIKE @parametername1
GO
EXEC mystoredprocedurename1
@parametername1 = '%a%'
---
---
Book: see also:
[book: Vieira, Robert - Professional SQL Server 2000 programming -
ISBN 1-861004-48-6 - p. 364 'Supplying Default Values']
---
[book: author: Payne, Chris - title: Teach yourself ASP.NET in 21
days - ISBN 0-672-32445-8 - p. 424 'Creating Stored procedures in SQL
Server 2000']
---
[book: author: Shepker, Matthew - title: Writing Stored Procedures for
Microsoft SQL Server - year: 2000 - ISBN: 0-672-31886-5]
---
---
Internet: see also:
---
Database: SQL: Query: Stored procedure: Can you give an overview of a
stored procedure?
http://www.faqts.com/knowledge_base/view.phtml/aid/27892/fid/147
----------------------------------------------------------------------
