–Section–	—SP Statements–
Part 1: Variables	Variables, Declare, Set, Global Variables
Part 2: Input Parameters	Input Parameters, Named Input Parameters, Optional Parameters
Part 3: Output Parameters	Output Parameters and Return
Part 4: Conditional Statements	If Else Blocks, Nested Conditions
Part 5: Table Valued Parameters	Table Valued Input Parameters
Part 6: Loop Statements	While Loop, Break, Continue, Cursors
[SQL and TSQL Queries]	Cheat Sheat for SQL and TSQL Queries
[SQL with Java JDBC]	Java JDBC for SQL and Stored Procedures

Download SQL Stored Procedure Sheet Free!

Part 1: Variables – Declare, Set, Global Variables

Clause	Type	Statement	Meaning
declare	no initializer	declare @count int;	declare a variable default value of the variable is set to null
declare	with assignment	declare @city varchar(50) = ‘London’;	declare a variable and give it a vlaue
set	plain value	set @height = 10.5;	set a static value in a variable
set	calculated value	set @weight = @baseWeight * 5;	set a calculated value in a variable
set	using query	set @myCount = (select count(*) from people);	set a value in a variable from the result of a select query the query should return only one value
query	variable as input to query	select fname,lname from customer where id = @userid	give any input value to the query form a variable
query	variable gets output from query	select @userid = id from customer where fname=’lola’;	set a value in a variable from one of the returned columns of a select query the query should return only one value
query	variable as both input and output	select @name=fname, @title=jobtitle from customer where id = @userid	multiple variable can be used in query some variables giving input value to the query some variables getting set based on different columns selected by the query
query	variable display as a column	select @sales as year_sales, @myCount as count;	display data in the varaible as select query result
query	variable as list of values	declare @empList nvarchar(max) set @empList = ” select @empList = @empList + name + ‘,’ from employee print @empList	all values in the column selected by the query can be appended to the variable in a single statement can a separator between values by appending character
global variables	system variables	select @@servername –returns name of server select @@version –returns SQL server version details select @@rowcount   –returns count of rows selected/updated from previous query	variable starting with @@ are global or system variables
print	message	print ‘Name is:’ + @name;	prints text in the messages tab of output
print	message	print ‘Calculated Distnace:’ + cast(@distance as nvarchar);	print needs all variables of same type. so cast might be required
print	message	print ‘Calculated Distnace:’ + format(@distance, ‘N2’);
print	message	print ‘Hi ”lola” how are you’	to print a quote character in the message, escape the quote with another quote

Part 2 : Input Parameters – Named Input Parameters, Optional Parameters

Clause	Type	Statement	Meaning
create	without parameters	create procedure spEmployees as begin   select empid, emp_name from employee; end	create a stored procedure without parameters
execute	without parameters	exec spEmployees	executes a stored procedure to display the result
create	with input parameters	create procedure spEmployees (   @department as varchar(30),   @employeeID as int ) as begin   select empid, emp_name   from employee   where emp_dept = @depatment   and empid = @employeeID; end	every parameter must begin with @ symbol can have multiple input parameter separated by comma
execute	with input parameters	exec spEmployees ‘sales’ , 121	executes a stored procedure all parameters must be supplied and separated by comma
execute	with named input parameters	exec spEmployees @department=’sales’ , @employeeID=121	executes a stored procedure named parameters add some clarity on the exec statement as to what does that parameter value mean
execute	with input parameters as other variables	declare @dept nvarchar(30) = ‘lola’; declare @empID int = 20 exec spEmployees @department=@dept , @employeeID=@empID	a calculated variable can be passed as input parameter to stored procedure
create	with input parameters and like clause	create procedure spEmployees (   @department as varchar(30) ) as begin   select empid, emp_name   from employee   where emp_dept like ‘%’+@depatment+’%’ end	variable name cannot be inside single quotes or else it will treated as string concat is used on varaible with % character as a string to performa a like clause search
create	with optional parameters -text default values	create procedure spEmployees (   @department as varchar(30) = ‘sales’,   @employeeID as int ) as begin   select empid, emp_name   from employee   where emp_dept = @depatment   and empid = @employeeID; end	givign a default value to a input parameter makes it optional during execution
execute	with optional parameters	exec spEmployees @employeeID=121	we can skip optional parameters  in the execute call for which a default value is provided in the stored procedure
create	with optional parameters -null default values	create procedure spEmployees (   @employeeID as int = null ) as begin   select empid, emp_name   from employee   where (@employeeID is null or empid = @employeeID); end	if input parameter is not provided during execution, the variable will be set as null if variable is set as null, the where clause should not filter and return all results if variable has a value, the where clause should perform filter and return filtered rows as per condition
execute	with optional parameters	exec spEmployees	all rows are returned since parameter is not provided and will be treated as null

Part 3 : Output Parameters – Return

Clause	Type	Statement	Meaning
create	with output parameters	create procedure spEmployees (   @department varchar(30),   @empList nvarchar(max) output,   @empCount int output ) as begin      declare @emp nvarchar(max);   set @emp = ”;   select @emp = @emp + emp_name + ‘,’   from employee   where emp_dept = @depatment;      set @empCount = @@rowcount;   set @empList = @emp;    end	1. output keyword after the parameter definition makes it an output parameter 2. there can be multiple output parameters in stored procedure 3. output varaible can have any data type
execute	with output parameters	declare @salesEmpList nvarchar(max) declare @salesEmpCount int exec spEmployees   @department = ‘sales’,   @empList = @salesEmpList output,   @empCount = @salesEmpCount output select   @salesEmpCount as sales_emp_count,   @salesEmpList as sales_emp_list	execute command needs to use named parameters and specify output keyword to transfer output data from stored procedure variable into local variable
create	with return statement	create procedure spEmployees (   @department varchar(30) ) as begin   select empid, emp_name   from employee   where emp_dept = @depatment;      return @@rowcount    end	1. return statement can only return one value from a stored procedure 2. return data type must be number
execute	with output parameters	declare @salesEmpCount int exec @salesEmpCount = spEmployees   @department = ‘sales’ select   @salesEmpCount as sales_emp_count	execute command needs to transfer the return of stored procedure into a local variable to use it

Part 4 : Conditional Statements – If Else

Clause	Type	Statement	Meaning
if	comparison operators	if @time > 0 begin   set @speed = @distance / @time   print @speed end
if	null check with is null	if @input is null begin   print ‘input is not provided’ end	is null = cehcks if value is null or not
if	null check with built-in func	if isnull(@input,”) = ” begin   print ‘input is not provided’ end	isnull(@input,”) =
if else	comparison operators	if @time > 0 begin   set @speed = @distance / @time   print ‘speed calculated’ end else begin   set @speed = 1000   print ‘speed set to max’ end
if else	single statements	if @time > 0   set @speed = @distance / @time else   set @speed = 1000	if there are only single statements in if and else block, then the begin and end keywords can be skipped
if else	nested	if @time > 0 begin   set @speed = @distance / @time   if @showDebug is not null begin     print ‘speed calculated’   end end else begin   set @speed = 1000   if @showDebug is not null begin     print ‘speed set to max’   end end
if else	with select and return	create procedure getEmployeeDetails (   @gender varchar(10) ) as begin   if @gender = ‘Male’ begin     (select * from employees where gender = ‘M’)     return   end   if @gender = ‘Female’ begin     (select * from employees where gender = ‘F’)     return   end   select ‘Invalid Input (Male or Female allowed)’ end	exec getEmployeeDEtails @gender=’Male’ will return Male employees exec getEmployeeDEtails @gender=’King’ will return Invalid Input statement

Part 5 : Table Valued Input Parameters

Clause	Type	Statement	Meaning
type	tvp	create type CategoryTableType as table  (   CategoryID int,   CategoryName nvarchar(50) )	create a table type and define the structure in SQL Server before you can use table-valued parameters
table valued parameter	tvp	create procedure sp_UpdateCategories (   @inputCategories CategoryTableType readonly )	declare table-valued parameters based on that type
update using tvp	tvp	update dbo.Categories   set c.CategoryName = ec.CategoryName   from dbo.Categories c join @inputCategories AS ic   on c.CategoryID = ic.CategoryID;	update existing table using tvp
insert using tvp	tvp	insert into dbo.Categories (CategoryID, CategoryName)   select ic.CategoryID, ic.CategoryName from @inputCategories AS ic;	insert into existing table using tvp
general	general	Limitations of Table Valued Parameters: Table-valued parameters are read-only in Transact-SQL code. You can’t update the column values in the rows of a table-valued parameter and you can’t insert or delete rows. To modify the data that is passed to a stored procedure or parameterized statement in table-valued parameter, you must insert the data into a temporary table or into a table variable. You can’t pass table-valued parameters to user defined functions.	tvp cannot be modified

Part 6 : Loop Statements – While and Cursor

Clause	Type	Statement	Meaning
while	comparison operators	declare @i int set @i = 1 while @i <= 10 begin   print @i   set @i = @i + 1 end	loop from @i = 1 to 10 print the value of @i in every iteration of loop
while	with select stmt	declare @salaryGroupStart int = 0 declare @salaryGroupEnd int = 0 declare @salaryIncrement int = 10000 declare @maxSalary int = 50000 declare @employeeCount int while @salaryGroupStart <= @maxSalary begin   set @salaryGroupEnd = @salaryGroupStart         + @salaryIncrement;   set @employeeCount = (select count(*) from dbo.employee         where salary >= @salaryGroupStart         and salary < @salaryGroupEnd);   print ‘Salary Group:’+cast(@salaryGroupStart as varchar)         +’ to ‘+cast(@salaryGroupEnd as varchar)         +’ Employee Count:’+cast(@employeeCount as varchar)   set @salaryGroupStart = @salaryGroupEnd end	Finds the number of employees having salary between a particular salary group Output: Salary Group:0 to 10000 Employee Count:0 Salary Group:10000 to 20000 Employee Count:2 Salary Group:20000 to 30000 Employee Count:1 Salary Group:30000 to 40000 Employee Count:0
while	break and continue	while (select avg(price) FROM product) < 300   begin    update product set price = price * 2;    if (select max(price) FROM product ) > 500         break    else       continue end	Double the Price of prodcuts till it reaches a maximum price If maximum price is reached break from the loop. Otherwise Continue the loop
cursor	with while loop	–step 1 declare variables declare @fname as nvarchar(50); declare @lname as nvarchar(50); –step 2 declare cursor declare @personCursor cursor for select fname,lname from person; –step 3 open cursor open @personCursor; –step 4 fetch first record from cursor into variables fetch next from @personCursor into @fname,@lname; –step 5 loop till we can fetch something from cursor while @@fetch_status = 0 begin   –step 6 do something with variables data from record   print @fname + ‘—‘ + @lname;   –step 7 fetch next record from cursor and go back to loop   fetch next from @personCursor into @fname,@lname; end –step 8 close the cursor close @personCursor –step 9 deacclocate cursor from memory deallocate @personCursor	Disadvantages: cursor is slow – every cursor loop will perform a new select statement. Overall it will take a lot of time to complete for big tables Alternatives: alternative to cursor is join – you can join and bring data together instead of one by one. Bringing / Procesing data by block is faster. Advantages: with cursor you can break millon record selects or updates into small blocks in a cursor. This way you can process smaller blocks at a time. Also if execution stops in between, part of the processing is completed and not everything is rolled back.

[Download PDF – Stored Procedures]