–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!
![](http://helpercodes.com/wp-content/uploads/2024/08/sql-kit-banner.jpeg)
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 |
message | print ‘Name is:’ + @name; | prints text in the messages tab of output | |
message | print ‘Calculated Distnace:’ + cast(@distance as nvarchar); | print needs all variables of same type. so cast might be required | |
message | print ‘Calculated Distnace:’ + format(@distance, ‘N2’); | ||
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. |
2 comments on SQL Stored Procedure Guide – Free Download!