SQL Server Stored Procedures - How to create it

To create a stored procedure, we use CREATE PROCEDURE statement. 

The basic syntax is as follows.

CREATE PROCEDURE procedure_name
(
    -- Parameters list
)
AS
BEGIN
    -- SQL statements here
END;

The header of stored procedure can have any of the following

• Input parameters
• Output parameters

Parameter will have the following

• Name
• Type
• Default value
• Input and Output

The body of stored procedure can have any of the following

• Variables
• Call to a function
• Call to a stored procedure
• Cursors
• DDL
• DML
• Temporary table
• If, While, Case statements
• Return statement

Note that stored procedure can be nested. The nesting can be up to 32 levels.

Convention: The name of stored procedure should begin with usp prefix and must not begin with sp prefix as all system stored procedures begin with sp. 

First we see an example of stored procedure without parameter. The stored procedure can create a table with three columns named as id, username and password in AppliedkDb database.

Example of Stored Procedure without Parameter

use AppliedkDb
Go

CREATE PROCEDURE usp_create_tbl_user
AS
BEGIN
CREATE TABLE tbl_user(
id int primary key identity(1,1)
,username nvarchar(30) NOT NULL
,password nvarchar(30) NULL
)
END
Go

Run the above script.

In the Programmability > Stored Procedure folder, we get the stored procedure created.

Executing Stored Procedure

To execute stored procedure, we use EXECUTE statement. The syntax is as follows.

EXECUTE < stored procedure name > <arguments-list>

In above example, stored procedure is created but is not executed yet. Executing the usp_create_tbl_user procedure will create the table.

EXEC usp_create_tbl_user

REMARK: When stored procedure is executed then a single line of code is sent from client to the server and hence the network traffic is very low and the performance of the stored procedure is better. When a stored procedure is executed first time, it takes time to create code execution plan but then and thereafter the code execution is very fast because the cached execution plan is used to execute the procedure. 

The execution plan of stored procedure depends upon a number of factors such as volume of data, presence and nature of index, comparison operators, and presence of joins, union, group by etc. in the procedure.

In the next post, we will learn how to create a stored procedure with parameter.