SQL Stored Procedure Guide – Free Download!

–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 StatementsIf Else Blocks, Nested Conditions
Part 5: Table Valued ParametersTable Valued Input Parameters
Part 6: Loop StatementsWhile 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

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

Part 5 : Table Valued Input Parameters

ClauseTypeStatementMeaning
typetvpcreate 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 parametertvpcreate procedure sp_UpdateCategories (
  @inputCategories CategoryTableType
readonly
)  
declare table-valued parameters based on that type
update using tvptvpupdate 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 tvptvpinsert into dbo.Categories
(CategoryID, CategoryName)  
select ic.CategoryID, ic.CategoryName
from @inputCategories AS ic;  
insert into existing table using tvp
generalgeneralLimitations 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

ClauseTypeStatementMeaning
whilecomparison operatorsdeclare @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
whilewith select stmtdeclare @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
whilebreak and continuewhile (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
cursorwith 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]

2 comments on SQL Stored Procedure Guide – Free Download!

Leave a Reply

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