How to create stored procedures

 

1. 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 5.sks 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 13. 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, 33. views 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. sksssssssss a sdfasdf a s 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> as begin <sql statements> end --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 as 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 as 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