SQL Server FAQs - Transact-SQL Understanding Stored Procedures--tutorial.jcwcn.com

Please note that all answers and tutorials are based on MS SQL Server 2005. To follow tutorials in this collection, you should connect to SQL server as a regular user who has enough permissions to run simple Transact-SQL statements. To follow tutorial exercises in this collection, you need to use test tables created from previous tutorial collections.

What Are Stored Procedures?

A stored procedure is a collection of Transact-SQL statements that stored in the SQL Server. A stored procedure can be executed later with an EXEC statement. SQL Server supports stored procedures with the following features:

1. Stored procedures can be divided into 3 groups based their accessing scopes:

Permanent Procedures - Created for permanent use.
Global Temporary Procedures - Created for temporary use within all sessions.
Local Temporary Procedures - Created for temporary use within the same session.

2. Stored procedures can take input parameters.

3. Stored procedures can take output parameters.

4. Stored procedures can be mapped to references to Microsoft .NET Framework common language runtime (CLR) methods.

How To Create a Simple Stored Procedure?

If you want to create a simple stored procedure with no input and output parameters, you can use the "CREATE PROCEDURE" command with a statement batch in a simple format as shown in below:

         
CREATE PROCEDURE procedure_name AS
   statement_1;
   statement_2;
   ...        
   statement_n;
GO

The following tutorial exercise shows you how to create a simple stored procedure:

         
USE FyiCenterData;
GO            

CREATE PROCEDURE Hello AS
   SELECT 'Welcome to:';
   SELECT '   FYIcenter.com';
GO
Command(s) completed successfully.

EXEC Hello;
GO

-----------
Welcome to;
(1 row(s) affected)

----------------
   FYIcenter.com
(1 row(s) affected)
How To Execute a Stored Procedure?
If you want execute a stored procedure created previously, you can use the EXECUTE statement in the following formats: 
EXEC procedure_name;
EXECUTE procedure_name;
The key word EXEC is actually optional. So you can execute a stored procedure by just entering the procedure name as the statement. See examples in the following tutorial exercise: 
USE FyiCenterData;
GO

-- create a quick procedure 
CREATE PROCEDURE date AS 
   PRINT CONVERT(VARCHAR(20),GETDATE(),107);
GO

-- execute with EXEC
EXEC date;
GO
May 19, 2007

-- execute with EXEC
date;
GO
May 19, 2007


-- using a reserved keyword as procedure name
CREATE PROCEDURE datetime AS PRINT GETDATE();
GO

datetime;
GO
May 19, 2007 11:35PM
Looks like SQL Server allows you to reserved keywords as stored procedure names.
How To List All Stored Procedures in the Current Database?
If you want to see a list of stored procedures in your current database, you can use the system view, sys.procedures as shown in this tutorial exercise: 
USE FyiCenterData;
GO

SELECT * FROM sys.procedures;
GO
Name       object_id   schema_id   type type_desc           
---------- ----------- ----------- ---- --------------------
Hello      1621580815  1           P    SQL_STORED_PROCEDURE
date       1653580929  1           P    SQL_STORED_PROCEDURE
datetime   1669580986  1           P    SQL_STORED_PROCEDURE

(3 row(s) affected)
How To Drop an Existing Stored Procedure?
If you have an existing procedure that you don't want to use it anymore, you should delete it from the SQL Server by using the "DROP PROCEDURE" statement as shown in the tutorial example below: 
USE FyiCenterData;
GO

DROP PROCEDURE datetime;
GO
Command(s) completed successfully.

SELECT * FROM sys.procedures;
GO
Name       object_id   schema_id   type type_desc           
---------- ----------- ----------- ---- --------------------
Hello      1621580815  1           P    SQL_STORED_PROCEDURE
date       1653580929  1           P    SQL_STORED_PROCEDURE

(2 row(s) affected)
Stored procedure "datetime" is no longer in the database. 
How To Create a Stored Procedure with a Statement Block?
If you are creating a stored procedure with multiple statements, it's better to use "BEGIN ... END" to group all statements into a single statement block. 
The tutorial exercise below shows you some good examples: 
USE FyiCenterData;
GO

CREATE PROCEDURE Show AS BEGIN
   SELECT name, type_desc FROM sys.tables;
   SELECT name, type_desc FROM sys.views;
   SELECT name, type_desc FROM sys.procedures;
END;
GO
Command(s) completed successfully.

EXEC Show;
GO
name                type_desc
------------------- ---------------------
fyi_random          USER_TABLE
fyi_links_indexed   USER_TABLE
fyi_links           USER_TABLE
fyi_links_copy      USER_TABLE

name                type_desc
------------------- ---------------------
fyi_links_top       VIEW
fyi_links_dump      VIEW
fyi_links_view      VIEW

name                type_desc
------------------- ---------------------
Hello               SQL_STORED_PROCEDURE
date                SQL_STORED_PROCEDURE
Show                SQL_STORED_PROCEDURE
How To End a Stored Procedure Properly?
Where the end of the "CREATE PROCEDURE" statement structure? The answer is simple, the end of the statement batch. 
Even if you are using a "BEGIN ... END" statement block, the stored procedure structure is not going to end at the end of the statement block. It will continue to the end of the statement batch, usually the GO command. The tutorial exercise gives you a good example: 
USE FyiCenterData;
GO

DROP PROCEDURE ShowFaq;
DROP TABLE Faq;
GO

-- How this statement batch will be executed?
CREATE PROCEDURE ShowFaq AS BEGIN
   PRINT 'Number of questions:';
   SELECT COUNT(*) FROM Faq;
   PRINT 'First 5 questions:'
   SELECT TOP 5 * FROM Faq;
END;
CREATE TABLE Faq (Question VARCHAR(80));
GO

EXEC ShowFaq;
GO
Number of questions:
Msg 208, Level 16, State 1, Procedure ShowFaq, Line 3
Invalid object name 'Faq'.
What happened here was that the "CREATE TABLE" statement was not execueted. It was included as part of the stored procedure "ShowFaq". This is why you were getting the error "Invalid object name 'Faq'." 
How To Generate CREATE PROCEDURE Script on an Existing Stored Procedure?
If you want to know how an existing stored procedure was created, you can use SQL Server Management Studio to automatically generate a "CREATE PROCEDURE" script The following tutorial shows you how to do this:
1. Run SQL Server Management Studio and connect to SQL server. 
2. On the Object Explorer window, follow the object tree: Databases > FyiCenterData > Programmability > Stored Procedures > dbo.ShowFaq. 
3. Click right mouse button on dbo.ShowFaq. The context menu shows up. 
4. Select "Script Stored Procedure as" > "CREATE to" > "New Query Editor Window". The following script will be displayed: 
USE [FyiCenterData]
GO
/****** Object:  StoredProcedure [dbo].[ShowFaq]
   Script Date: 05/19/2007 21:31:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[ShowFaq] AS BEGIN
   PRINT 'Number of questions:';
   SELECT COUNT(*) FROM Faq;
   PRINT 'First 5 questions:'
   SELECT TOP 5 * FROM Faq;
END;
CREATE TABLE Faq (Question VARCHAR(80));
How To Get the Definition of a Stored Procedure Back?
If you want get the definition of an existing stored procedure back from the SQL Server, you can use the system view called sys.sql_modules, which stores definitions of views and stored procedures. 
The sys.sql_modules holds stored procedure definitions identifiable by the object id of each view. The tutorial exercise below shows you how to retrieve the definition of stored procedure, "ShowFaq" by joining sys.sql_modules and sys.procedures: 
USE FyiCenterData;
GO

SELECT m.definition 
   FROM sys.sql_modules m, sys.procedures p
   WHERE m.object_id = p.object_id
   AND p.name = 'ShowFaq';   
GO
definition
-----------------------------------------
CREATE PROCEDURE ShowFaq AS BEGIN
   PRINT 'Number of questions:';
   SELECT COUNT(*) FROM Faq;
   PRINT 'First 5 questions:'
   SELECT TOP 5 * FROM Faq;
END;
CREATE TABLE Faq (Question VARCHAR(80));
(1 row(s) affected)
How To Modify an Existing Stored Procedure?
If you find a mistake in an existing stored procedure previously created, you can drop (delete) it and create it again correctly. But dropping a stored procedure may affect other database objects who are depending on this stored procedure. 
So the best way to correct a mistake in an existing stored procedure is to use the "ALTER PROCEDURE" statement as shown in the following tutorial example: 
USE FyiCenterData;
GO

-- Finding a mistake - the last line is wrong
SELECT m.definition 
   FROM sys.sql_modules m, sys.procedures p
   WHERE m.object_id = p.object_id
   AND p.name = 'ShowFaq';   
GO
definition
-----------------------------------------
CREATE PROCEDURE ShowFaq AS BEGIN
   PRINT 'Number of questions:';
   SELECT COUNT(*) FROM Faq;
   PRINT 'First 5 questions:'
   SELECT TOP 5 * FROM Faq;
END;
CREATE TABLE Faq (Question VARCHAR(80));
(1 row(s) affected)

-- Modifying the stored procedure
ALTER PROCEDURE ShowFaq AS BEGIN
   PRINT 'Number of questions:';
   SELECT COUNT(*) FROM Faq;
   PRINT 'First 5 questions:'
   SELECT TOP 5 * FROM Faq;
END;
GO
Command(s) completed successfully.

-- Confirmation of the correction
SELECT m.definition 
   FROM sys.sql_modules m, sys.procedures p
   WHERE m.object_id = p.object_id
   AND p.name = 'ShowFaq';   
GO
definition
-----------------------------------------
CREATE PROCEDURE ShowFaq AS BEGIN
   PRINT 'Number of questions:';
   SELECT COUNT(*) FROM Faq;
   PRINT 'First 5 questions:'
   SELECT TOP 5 * FROM Faq;
END;
(1 row(s) affected)
How To Create Stored Procedures with Parameters?
Very often, you need to create a stored procedure with one or more parameters. You only supply values to those parameters at the time of executing the stored procedure. 
Stored procedures with parameters can be created with the following syntax: 
CREATE PROCEDURE procedure_name 
   @parameter_1 datatype, 
   @parameter_2 datatype, 
   ...
   @parameter_n datatype
AS
   statement_1;
   statement_2;
   ...
   statement_n;
GO
The following tutorial exercise shows you how to create a stored procedure with one parameter called @url: 
USE FyiCenterData;
GO

DROP PROCEDURE Hello;
GO

CREATE PROCEDURE Hello 
   @url nvarchar(40)
AS
   PRINT 'Welcome to ' + @url;
GO

EXEC Hello 'dba.fyicenter.com';
GO
Welcome to dba.fyicenter.com
How To Provide Values to Stored Procedure Parameters?
If a stored procedure is created with parameters, you need pass values to those parameters when calling the stored procedure with one of two formats listed below: 
-- Passing values only
EXEC procedure_name value_1, value_2, ... value_n;

-- Passing name-value pairs
EXEC procedure_name 
   @parameter_1 = value_1, 
   @parameter_2 = value_2, 
   ... 
   @parameter_n = value_n;
The tutorial exercise below shows 2 ways to pass values to stored procedure parameters: 
DROP PROCEDURE Hello;
GO

CREATE PROCEDURE Hello 
   @url nvarchar(40)
AS
   PRINT 'Welcome to ' + @url;
GO

EXEC Hello 'dba.fyicenter.com';
GO
Welcome to dba.fyicenter.com

EXEC Hello @url='dev.fyicenter.com';
GO
Welcome to dev.fyicenter.com
What Are the Advantages of Passing Name-Value Pairs as Parameters?
When calling a stored procedure defined with parameters, you can pass values to those parameters in two ways: 
Passing only values in the same order as parameters defined in the stored procedure. 
Passing name-value pairs in any order. 
The advantages of passing name-value pairs to stored procedure parameters are: 
Makes the calling statement more readable - You know which value is passed to which parameter. 
Makes it possible to pass values in an order different than how parameters are defined. 
The tutorial exercise shows you some good examples of passing name-value pairs as parameters: 
CREATE PROCEDURE diff_in_days 
   @start_date DATETIME,
   @end_date DATETIME
AS BEGIN
   PRINT CONVERT(VARCHAR(20),@end_date,107) 
      + ' - ' 
      + CONVERT(VARCHAR(20),@start_date,107)
      + ' = ' 
      + STR(DATEDIFF(DAY, @start_date, @end_date));
END;
GO

EXEC diff_in_days 
   '01-Jan-2007', 
   '19-May-2007';
GO
May 19, 2007 - Jan 01, 2007 =        138

EXEC diff_in_days 
   @start_date='01-Jan-2007', 
   @end_date='19-May-2007';
GO
May 19, 2007 - Jan 01, 2007 =        138

-- Name-value pairs can be given in any order
EXEC diff_in_days 
   @end_date='19-May-2007',
   @start_date='01-Jan-2007'; 
GO
May 19, 2007 - Jan 01, 2007 =        138
Can You Pass Expressions to Stored Procedure Parameters?
Can you pass expressions to stored procedure parameters? The answer is no. 
When executing stored procedures, all input values must be entered as data literals, which can be specified within single quotes ('), or without them if they cause no confusion. The tutorial exercise below shows you how input values should be specified: 
CREATE PROCEDURE area_of_circle @radius REAL
AS BEGIN
   PRINT 'Radius = ' + STR(@radius,9,3);
   PRINT 'Area = ' + STR(3.14*@radius*@radius,9,3);
END;
GO

-- Input value without quotes
EXEC area_of_circle 1.5;
GO
Radius =     1.500
Area =     7.065

-- Input value with quotes 
EXEC area_of_circle '1.5';
GO
Radius =     1.500
Area =     7.065

-- Expressions are not allowed
EXEC area_of_circle 1.0+0.5;
GO
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '+'.
How To Provide Default Values to Stored Procedure Parameters?
If you add a parameter when creating a stored procedure, you can provide a default value so that the execution statement is not required to pass input value to this parameter. 
To provide a default value to a parameter, you should use this format: "@parameter_name data_type = default_value". The tutorial exercise below shows you how provide default values to stored procedure parameters: 
USE FyiCenterData;
GO

DROP PROCEDURE diff_in_days;
GO

CREATE PROCEDURE diff_in_days 
   @start_date DATETIME,
   @end_date DATETIME = '19-May-2007'
AS BEGIN
   PRINT CONVERT(VARCHAR(20),@end_date,107) 
      + ' - ' 
      + CONVERT(VARCHAR(20),@start_date,107)
      + ' = ' 
      + STR(DATEDIFF(DAY, @start_date, @end_date));
END;
GO

-- Default value is used
EXEC diff_in_days
   @start_date='01-Jan-2007'; 
GO
May 19, 2007 - Jan 01, 2007 =        138

-- Default value is not used
EXEC diff_in_days
   @start_date='01-Jan-2007', 
   @end_date='11-May-2007';
GO
May 11, 2007 - Jan 01, 2007 =        130

-- Input value must be supplied for a parameter 
-- without a default value
EXEC diff_in_days
   @end_date='11-May-2007';
GO
Msg 201, Level 16, State 4, Procedure diff_in_days, Line 0
Procedure or Function 'diff_in_days' expects 
parameter '@start_date', which was not supplied.
How To Define Output Parameters in Stored Procedures?
Sometime a stored procedure not only want to take input values from the calling statement batch, but it also want to send output values back to the calling statement batch. This can be done by defining output parameters in the CREATE PROCEDURE statement. 
To define an output parameter, you should use this format: "@parameter_name data_type OUTPUT", as shown in the following tutorial exercise: 
DROP PROCEDURE diff_in_days;
GO

-- Defining an output parameter
CREATE PROCEDURE diff_in_days 
   @start_date DATETIME,
   @end_date DATETIME = '19-May-2007',
   @days VARCHAR(40) OUTPUT
AS BEGIN
   SET @days = CONVERT(VARCHAR(20),@end_date,107)
      + ' - ' 
      + CONVERT(VARCHAR(20),@start_date,107)
      + ' = ' 
      + STR(DATEDIFF(DAY, @start_date, @end_date));
END;
GO
Command(s) completed successfully.

EXEC diff_in_days
   @start_date='01-Jan-2007'
GO
Msg 201, Level 16, State 4, Procedure diff_in_days, Line 0
Procedure or Function 'diff_in_days' expects 
parameter '@days', which was not supplied.
How To Receive Output Values from Stored Procedures?
If an output parameter is defined in a stored procedure, the execution statement must provide a variable to receive the output value in the format: "@variable_name OUTPUT" or "@parameter_name = @variable_name OUTPUT". The following tutorial exercise gives you a good example: 
-- Using @variable format
DECLARE @message VARCHAR(40);
EXECUTE diff_in_days
   '01-Jan-2007',
   '11-May-2007',
   @message OUTPUT;
PRINT @message;
GO
May 11, 2007 - Jan 01, 2007 =        130

-- Using @parameter = @variable format
DECLARE @message VARCHAR(40);
EXEC diff_in_days
   @start_date='01-Jan-2007',
   @end_date='11-May-2007',
   @days = @message OUTPUT;
PRINT @message;
GO
May 11, 2007 - Jan 01, 2007 =        130

-- Mixed formats are not allowed
DECLARE @message VARCHAR(40);
EXEC diff_in_days
   @start_date='01-Jan-2007',
   @end_date='11-May-2007',
   @message OUTPUT;
PRINT @message;
GO
Msg 119, Level 15, State 1, Line 2
Must pass parameter number 2 and subsequent parameters 
as '@name = value'. After the form '@name = value' has 
been used, all subsequent parameters must be passed in 
the form '@name = value'.
How To Create a Local Temporary Stored Procedure?
A local temporary stored procedure is a special stored procedure that: 
Is created like a normal (permanent) stored procedure with the name prefixed with a number sign (#). 
Are only valid in the same client session where it was created. 
Will be deleted when creating session is terminated. 
This tutorial exercise here creates two stored procedures, one is permanent and the other is local temporary: 
DROP PROCEDURE Hello;
DROP PROCEDURE #Hello;
GO

CREATE PROCEDURE Hello 
   @url nvarchar(40)
AS
   PRINT 'Welcome to ' + REVERSE(@url);
GO

CREATE PROCEDURE #Hello 
   @url nvarchar(40)
AS
   PRINT 'Welcome to ' + @url;
GO

EXECUTE Hello 'fyicenter.com';
GO
Welcome to moc.retneciyf

EXECUTE #Hello 'fyicenter.com';
GO
Welcome to fyicenter.com
Can Another User Execute Your Local Temporary Stored Procedures?
Can another user execute your local temporary stored procedures? The answer is no. 
To test this out, continue with the exercise from the previous tutorial. Keep that user session running and open a new client session with sqlcmd.exe. Then run the tutorial script below: 
-- Executing permanent procedure 
-- created by another user session
EXECUTE Hello 'fyicenter.com';
GO
Welcome to moc.retneciyf

-- Executing local temporary procedure 
-- created by another user session
EXECUTE #Hello 'fyicenter.com';
GO
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure '#Hello'.

discuss this topic to forum

[Print][Close][Bookmark:digg+,reddit+,del.icio.us+,dzone+]

SQL Server FAQs - Transact-SQL Understanding Stored Procedures

relation tutorial

Category

New

Hot