Stored Procedure – with example

What is Stored Procedure?

• A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again.
• So if you have an SQL query that you write over and over again, save it as a stored procedure, and then just call it to execute it.
• You can also pass parameters to a stored procedure, so that the stored procedure can act based on the parameter value(s) that is passed.

Stored Procedure Syntax

CREATE PROCEDURE procedure_name

AS

Sql_statement

GO;

Execute a Stored Procedure

EXEC procedure_name;

To write procedure select database -> right click->new query

Types to pass parameters

• 1. Stored procedure with no parameter
• 2.Stored procedure with single parameter
• 3. Stored procedure with multiple parameters

Eg: Using Adventureworks Database

Stored procedure with one parameter

CREATE PROCEDURE [dbo].[ProductCategory_procedure]

AS
SELECT  * FROM Production.ProductCategory
GO

EXEC ProductCategory_procedure

Once written and executed  your create procedure check in Db->Programmability->Stored Procedure->your procedure name

Output for procedure with no parameter

With One parameter

CREATE PROCEDURE  [dbo].[oneparameter]
@ProductCatergoryID int   <- (parameter declared)

AS
SELECT  * FROM Production.ProductCategory pc WHERE pc.ProductCategoryID = @ProductCatergoryID
GO

EXEC multiparameter @ProductCatergoryID = ‘4’ <-parameter passed

Here @ProductCatergory parameter is passed to the procedure

With multi-parameter

CREATE PROCEDURE  [dbo].[multiparameter]
@ProductCatergoryID int, @Name varchar(50)  <- 2 parameters declared

AS
SELECT * FROM Production.ProductCategory pc WHERE pc.ProductCategoryID = @ProductCatergoryID and pc.Name = @Name
go

EXEC  multiparameter @ProductCatergoryID = ‘4’ , @Name = ‘Accessories’

<-2 parameters value passed

Alter Procedure.

Syntax:

ALTER PROCEDURE procedure_name

AS

Sql code

Go

Eg: I am using multi-parameter procedure

ALTER PROCEDURE [dbo].[multiparameter]
@ProductCatergoryID int, @Name varchar(50)

AS

(In previous procedure i used select * , here i am selecting only one column)
SELECT pc.rowguid FROM Production.ProductCategory pc WHERE pc.ProductCategoryID = @ProductCatergoryID and pc.Name = @Name

GO

EXEC multiparameter @ProductCatergoryID = ‘4’ , @Name = ‘Accessories’

Output for altered procedure