Wednesday, April 15, 2015

How to create stored procedures



1. About SQL server , compared to other products

2. go through SSMS

3. create 3 tables with queries

4. create a table with identity property


6. primary key , foreign key ,unique keys

7. create check constraints

8. create default constraints , not null property

9. insert

10. select

11. where clause

12. select


14. inline queries and sub queries

15. Joins

16. cursors

17. inbuilt functions (string)

18. inbuilt functions(handling nulls)

19. inbuilt function

20. grouping

21. other inbuilt functions

22. unioning other tables

23. update

24. delete

25. indexes

26. procedures

27. control flow

28. temp tables vs table variables

29. error handling

30. functions

31. triggers

32. collation,


34. synonyms

35. transactions

36. top(x),ranking,pivot,

37. optimize queries

38. Audit

About SQL server , compared to other products

Previous                          Next
Introduction : stored procedures contains a collection of SQL statements.
These are compiled  when it executes first time and creates an execution plan binds to the stored procedure. so the next time when the stored procedure executes , the Query Optimizer(Internal component) uses the execution plan.
procedure has following parts :
1)SQL Sttement in the body of the procedure
2)return value
3) Input Parameter
4)Output Parameters
1) basic syntax : this is a very basic syntax with out using return,input/output parameters
create procedure <name>
<sql statements>

--create a test table to create a stored procedure

create table customers (cid int , cname varchar(30),cage int,ccity varchar(30),cstate varchar(30))

--Insert 4 Records
insert into customers select 1,'c1',20,'city_1','state-1'
insert into customers select 2,'c2',20,'city_2','state-2'
insert into customers select 3,'c3',20,'city_3','state-3'
insert into customers select 4,'c4',20,'city_4','state-4'
-- Test how many records
Select * from customers
-- create a procedure to wrap the basic select statement above

create procedure pr_customers
select * from customers

-- basic syntax :execute procedure
exec pr_customers

Results :
--2) procedure with return parameter:
every procedure returns an integer value , by default the return value is 0 for successful execution.
execute the above procedure and print the return value
-- Syntax to execute a procedure to get the return value

declare @ret int -- declare a variable to capture the return value
exec @ret = pr_customers
select  @ret as return_value -- this is the return value of the procedure

so , default return value of 0 means its succesful.
create another stored procedure with a sql statement to generate error with 100/0 which results an erro

create proc pr_customers_with_ret
select 100/0

-- execute this procedure
declare @ret int
exec @ret = pr_customers_with_ret
select @ret as return_val

result : it results an error
click on the results tab as highlighted above. it will show the return value as below

Post a Comment

Featured Post

SQL Server AWS Migration BCP

stored procedure to generate BCP scritps to migrate the SQL Server database. Developed this stored procedure on my labs to simulate the Mi...