Sample Stored Procedure example

Sample Stored Procedure example

Stored procedure is a bunch of SQL statements which forms a logical unit and execute for a particular task and it’s used to encapsulate set of operations or queries to execute on database server. Once stored procedure created it gets compiled and executed with different parameters with results at run time. It can have any combination of output, input, output and input/output parameters. There are many different name used for Stored procedure example: SP, StoreProc, proc, StoredProc, sproc, proc etc…

Stored procedures also return result sets which is results of select statement on the tables. You can also defined variables for processing data and cursors which allows to loop through multiple rows on the table. Stored procedure flow control statements generally includes CASE statements, LOOP, IF, WHILE, REPEAT statements and more.

To understand better let’s create on sample table:

• Create table scripts:

create table dbo.Test (
	test_id int identity not null,
	smID varchar (20) null,
	eventDate date null,
	createTime datetime default getdate() null,
	updateTime datetime null,
	primary key (test_id) 
)

• Insert data script:

INSERT INTO dbo.Test(
   smID
  ,eventDate
  ,updateTime
) VALUES (
   '987456'  -- smID - IN varchar(20)
   ,'20151226' -- eventDate - IN date
   ,'12/26/2015 12:00:00 AM' -- updateTime - IN datetime
)

• Now we have created table Test with some dummy data. To get data from the table we use below select statement:

select * from dbo.Test

• This will give below results as I have inserted 4 rows of data:

• Now to convert select statement to stored procedure is fairly simple as below:

create procedure TestStoredProcedure
as
select * from dbo.Test
go

• If you execute newly created stored procedure it will also give same results as select statement:

execute dbo.TestStoredProcedure

If you want to include input parameter which will be included in select statement where clause as below:

• Select statement with where clause:

select * from dbo.Test where test_id in (1,2)

• Stored procedure:

create procedure TestStoredProcedure @param_1 int,@param_2 int
as
select * from dbo.Test where test_id in (@param_1,@param_2)
go

• Execute above stored procedure will give same result as select statement:

execute dbo.TestStoredProcedure 1, 2

Reference:

• Using Stored Procedures