SQL Stored Procedure Cheat Sheet PDF

–Section–SP Statements–
Part 1: VariablesVariables, Declare, Set, Global Variables
Part 2: Input ParametersInput Parameters, Named Input Parameters, Optional Parameters
Part 3: Output ParametersOutput Parameters and Return
Part 4: Conditional StatementsConditional Statements – If Else
[SQL and TSQL Queries]Cheat Sheat for SQL and TSQL Queries
[Java JDBC Connectivity]Java JDBC for SQL and Stored Procedures
Stored Procedures PDF Download

Send download link to Email:

Part 1: Variables – Declare, Set, Global Variables

ClauseTypeStatementMeaning
declareno initializerdeclare @count int;declare a variable
default value of the variable is set to null
declarewith assignmentdeclare @city varchar(50) = ‘London’;declare a variable and give it a vlaue
setplain valueset @height = 10.5;set a static value in a variable
setcalculated valueset @weight = @baseWeight * 5;set a calculated value in a variable
setusing queryset @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
queryvariable as
input to query
select fname,lname
from customer
where id = @userid
give any input value to the query form a variable
queryvariable 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
queryvariable 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
queryvariable display
as a column
select
@sales as year_sales,
@myCount as count;
display data in the varaible as select query result
queryvariable 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 variablessystem variablesselect @@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
printmessageprint ‘Name is:’ + @name;prints text in the messages tab of output
printmessageprint ‘Calculated Distnace:’ + cast(@distance as nvarchar);print needs all variables of same type. so cast might be required
printmessageprint ‘Calculated Distnace:’ + format(@distance, ‘N2’);
printmessageprint ‘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

ClauseTypeStatementMeaning
createwithout parameterscreate procedure spEmployees
as begin
  select empid, emp_name from employee;
end
create a stored procedure without parameters
executewithout parametersexec spEmployeesexecutes a stored procedure to display the result
createwith input parameterscreate 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
executewith input parametersexec spEmployees ‘sales’ , 121executes a stored procedure
all parameters must be supplied and separated by comma
executewith named input parametersexec spEmployees @department=’sales’ , @employeeID=121executes a stored procedure
named parameters add some clarity on the exec statement as to what does that parameter value mean
executewith input parameters as other variablesdeclare @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
createwith 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
createwith 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
executewith optional parametersexec spEmployees @employeeID=121we can skip optional parameters  in the execute call
for which a default value is provided in the stored procedure
createwith 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
executewith optional parametersexec spEmployeesall rows are returned since parameter is not provided and will be treated as null

Part 3 : Output Parameters – Return

ClauseTypeStatementMeaning
createwith output parameterscreate 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
executewith output parametersdeclare @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 statementcreate 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
executewith output parametersdeclare @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

ClauseTypeStatementMeaning
ifcomparison operatorsif @time > 0 begin
  set @speed = @distance / @time
  print @speed
end
ifnull check
with is null
if @input is null begin
  print ‘input is not provided’
end
is null = cehcks if value is null or not
ifnull check
with built-in func
if isnull(@input,”) = ” begin
  print ‘input is not provided’
end
isnull(@input,”) =
if elsecomparison operatorsif @time > 0 begin
  set @speed = @distance / @time
  print ‘speed calculated’
end else begin
  set @speed = 1000
  print ‘speed set to max’
end
if elsesingle statementsif @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 elsenestedif @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 elsewith select and returncreate 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

2 comments on SQL Stored Procedure Cheat Sheet PDF

Leave a Reply

Your email address will not be published. Required fields are marked *