SQL Server Stored Procedure example

Small, with parameters

if (exists (SELECT * from sys.objects where name = ' proc_get_purchasing2 '))

drop proc Proc_get_student

Go

ALTER proc PROC_GET_PURCHASING2 (@today int)

As

--declare @today int

Declaration variables

declare @weeks nvarchar (3)

Set Variable value set @weeks

Set @weeks =case

when @today = 1 Then ' Monday '

When @today = 2 Then ' Tuesday '

Else ' ERROR '

End

Print @weeks

EXEC proc_get_purchasing2 @today =2

With loops

if (exists (SELECT * from sys.objects where name = ' Proc_get_purchasing3 '))

drop proc Proc_get_purchasing3

Go

Create proc Proc_get_purchasing3

As

DECLARE @sum1 int

Set @sum1 =1

While @sum1 <10

Begin

Set @sum1 [Email protected]+1

End

EXEC proc_get_purchasing3

SQL Server Stored Procedure example