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?

17 of 19 people (89%) answered Yes
Recently 8 of 10 people (80%) answered Yes

Entry

Database: SQL: Run: Simple: How execute stored procedure in Microsoft SQL server, using SQL?

Dec 31st, 2003 18:44
Knud van Eeden,


----------------------------------------------------------------------
--- Knud van Eeden --- 01 January 2004 - 01:55 am --------------------
Database: SQL: Run: Simple: How execute stored procedure in Microsoft 
SQL server, using SQL?
---
Use the SQL command
 EXEC <your stored procedure name>
  @<your parameter name1> = <your parameter value1>,
  @<your parameter name2> = <your parameter value2>,
  @<your parameter name3> = <your parameter value3>,
  ...
  @<your parameter namelast> = <your parameter valuelast>
---
e.g.
 EXEC mystoredprocedurename1
  @parametername1 = '*',
  @parametername2 = 'Person',
  @parametername3 = 'John'
---
---
To indicate which database to use, type the command
first:
 USE <your databasename>
 GO
---
e.g.
 USE Emaillist
 GO
 EXEC mystoredprocedurename1
  @parametername1 = '*',
  @parametername2 = 'Person',
  @parametername3 = 'John'
---
---
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%'
---
---
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.
---
---
Book: see also:
[book: author: Vieira, Robert - title: Professional SQL Server 2000 
programming - ISBN 1-861004-48-6 - p. 365 'Creating output parameters']
---
[book: author: Groff, James R. / Weinberg, Paul N. - title: SQL (the 
complete reference) - publisher: McGraw-Hill - year: 1999 - ISBN 0-07-
211845-8 - p. 689 'Calling a stored procedure']
---
[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']
---
---
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
----------------------------------------------------------------------