Stored Procedure in MS Sql Server 2008

Stored Procedure is set of sql statement or pl/sql programming also.which is perform a specific task. when we compile the stored procedure then one object of the stored procedure will be created in the database.There is no need to compile stored procedure again and again.There are some differences between stored procedure and function .

Create a simple procedure:

create procedure prcselect
     as
select *from student

Execute procedure : There are some ways to execute the stored procedure:

execute prcselect
     or
exec prcselect
      or
prcselect

we can make stored procedure as option by specify the default value at the time of declaration.
Example:
First create a student table in your database and insert some values also as given below.
see it:

Create a stored procedure and Execute it

create procedure prcselect(@id int=0)
as
begin
if @id <>0
begin
select*from student where sid=@id
end
else
select*from student
end

Calling the stored procedure:
span style="font-family: Georgia, Times New Roman, serif;">

execute prcselect

execute prcselect 

see output:

• we can make stored procedure as optional by specify the default value at the time of declaration:

         Example:

  In this example i have altered previous stored procedure.After alter this again execute for create a new object .

alter procedure prcselect(@id int)
as
begin
declare @a int
select @a=age from student where sid=@id
return @a
end

Now write the following code and execute it which is given below:

declare @age int
exec @age=prcselect 104
print @age

output:

Note:  In this above example stored procedure is returning only integer value.

• Use of Out parameter in stored procedure:

see it:

alter  procedure prcselect(@id int,@name varchar(30)out,@sage int out)
as
begin
 select @name=name,@sage=age from student where sid=@id
end

calling the Stored procedure:
see it:

declare @sage int
declare @name varchar (30)
exec prcselect 106,@name out,@sage out
print 'name is  ' +@name+'  age is  '+cast(@sage as varchar(3))

Output:

Note:-Cast and convert are two function which can be used to convert a particular type of value into another type.
Benefits of stored procedure:
There are some benefits of stored procedure.which is give below:

1. No need to recompile the code again and again.
2. It is also more helpful for security purpose.
3. Reduce the Sql injection problem.
4. It is faster than function.

For More:-

1. Array in C#
2.   Ms Sqlserver
3.   Data Integrity
4.    Host ASP.NET Application on Server Free

I hope this is helpful for you.
To Get the Latest  Free Updates Subscribe
Click below to download all codes.
      Download