faqts : Computers : Databases : Microsoft SQL Server

+ Search
Add Entry AlertManage Folder Edit Entry Add page to http://del.icio.us/
Did You Find This Entry Useful?

5 of 11 people (45%) answered Yes
Recently 4 of 10 people (40%) answered Yes

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
----------------------------------------------------------------------