Stored Procedure :

-> Stored procedures are compiled queries.(query execution plan will be cached in the database) -> SP will be compiled only for one time and will be executed multiple times without recompilation (without generating the execution plan multiple times) -> SP can reduce the network traffic. -> SP can improve the security by hiding the underlying tables. SP is almost equivalent to a Function in c# or in any other programming language.

Features of Stored Procedure :

-> Accept input parameters and returns multiple values in the form of output parameters to the calling procedure and batch. -> Contain programming statements that perform operations in the database, including calling other procedures. -> Return a status value to a calling procedure or batch to indicate success or failure (and the reason for failure).While using return statements we can return only integers.

Types of Stored Procedure :

-> System stored procedure(whose name starts with SP_) -> User Defined stored procedures. -> Extended stored procedure. -> CLR stored procedure. Note --> We can write DML/DQL/DDL statements in a stored procedure. --> We can return data from SP in 3 ways *Using select statement *Using output statement *return statement(only numbers or integer values we can return). Syntax: create proc/procedure ( @variablename1 datatype[(size)][in/out], @variablename1 datatype[(size)][in/out], . . ) as begin - - T-SQL statements - end Syntax used for calling SP Execution of SP:EXEC SPName _,_,.... EX:Consider the below given students and states tables
sidnamestateid
1Rajeev2
2Mahesh4
3Sudheer1
stidstname
1KA
2UP
3TN
4OR
5MH
create proc insertstudents ( @sid int, @name varchar(40), @stid int ) as begin insert into students values(@sid,@name,@stid) end EXEC insertstudent 4,'bharath',3 Note: There can be procedures which may not be having input and output parameters. Q) Write a SP for updating students details create proc update students ( @sid int, @name varchar(40), @stid int, ) as begin update students set name=@name; stid=@sid where sid=@sid; end Q)Write a query for deleting students assuming(sid) is supplied to the SP create proc deletestudent ( @sid int ) as begin delete students where sid=@sid; end