Stored Procedure

 This article describe stored procedure and practical use of it.

Stored procedure is a wrapper and inside this wrapper you can write sql commands like select, update, delete etc.

Question here why need of writing SQL-commands inside stored procedure, basically there are several benefit of it writing SQL-commands inside stored procedure show below.

1. Stored procedure is pre-compiled and stored in database.

2. We can reuse stored procedure which is stored on database.

3. Avoid SQL injection as writing sql commands directly from application is risky.

4. Application using hard code sql commands inside application and in future any change in sql command then need to deploy new build.

5. Reduced server/client network traffic as client passing just name of stored procedure and not the sql commands directly from client to SQL server.

Syntax :

CREATE PROCEDURE <Procedure_Name> 

<ParameterName> <DataType>,...

AS

BEGIN

    -- SQL Commands like select, insert , etc..

GO

CREATE TABLE Student

(

id int primary key IDENTITY(1,1) NOT NULL,

name varchar(10) NULL,

city varchar(10) NULL

)

Insert into  Student values ('Sammer', 'Mumbai')

Insert into  Student values ('Paul', 'Nagpur')

Insert into  Student values ('Javed', 'Chennai')

Insert into  Student values ('Yogesh', 'Delhi')

We will create simple stored procedure here 

CREATE PROCEDURE uspGetStudents

AS

BEGIN

    Select * from [dbo].[Student]

End

To execute stored procedure in MS SQL server we use below command.

exec uspGetStudents 

OR

execute uspGetStudents

There are different types of stored procedure

1. Parameterized procedure 

2. Optional parameter procedure

3. Procedure with return keyword

4. Procedure with Output parameter 

Parameterized procedure : pass parameter to filter SQL-Command in procedure

Create procedure uspGetStudentById

@Id int

As

Begin

    Set Nocount ON

    select * from Student where id=@id

End

exec uspGetStudentById 1

Optional parameter procedure : here passing parameter is optional by default stored procedure uses Id=1 if parameter id not passed in below procedure

Alter procedure uspGetStudentByIdOptionalParam

@Id int = 1

As

Begin

Set Nocount ON

    select * from Student where id=@id

End

exec uspGetStudentByIdOptionalParam  -- Id parameter not passed 

exec uspGetStudentByIdOptionalParam 2 -- Id parameter passed 

Procedure with return keyword: 

Stored procedure returns value with return statement but only integer and single value shown below. 

Return statement does not support any other datatype except integer, 

For decimal it returns floor integer number 

Alter procedure getStudentWithReturn

As

Begin

return (select count(*) from Student)

--return (3.14)

End

Declare @count int

exec @count = getStudentWithReturn

select @count

Procedure with Output parameter :  procedure allows multiple output parameter with different data types.

CREATE PROCEDURE uspgetStudentByIdAndRowCount (

    @id int,

    @Row_count INT OUTPUT -- output parameter

) AS

BEGIN

Set Nocount ON

    select * from Student where id=@id

-- Row_count holds number of rows processed

    SELECT @Row_count = @@ROWCOUNT;

END;

Declare @count int

exec uspgetStudentByIdAndRowCount 1,@count output

select @count 

Stored procedure can have one or more SQL-Commands (select,Insert,Update, Delete ...). In this article I shown single select in procedure but procedure can return multiple select statement shown below.

Create procedure Test

As 

Begin

select getdate()

select * from Student

End

exec Test

Set Nocount ON : By default it is off, it is recommended to Set NoCount On before executing Sql query. Executing sql script in ms sql server returns number of rows processed/affected by query in message pane. Number of rows affected message is performance hit for cursor or in loop. Microsoft recommend to use  Set Nocount ON.

Note : Do not create stored procedure name with SP_  prefix as system procedure from Microsoft uses SP_  prefix .