Thursday, 14 March 2013

Stored procedure in Sql Server

Written By:- Isha Malhotra
Our Website:-Tech Altum

Stored Procedure
The stored procedure is a set of SQL Statements. In stored procedure we can accept the input and return the output. It is precompiled object. Stored procedure increases the performance of the database As they are recompiled.

Syntax of Stored Procedure:-

Create procedure Proc_Name
As
Begin
                SQL Statements
End

Example of Stored Procedure
Following is a procedure in which we select The data from table student
Create proc Proc_Select
As
Begin
Select * from student;
End

To execute the procedure We have to run the following command.
Execute Proc_name
After running this procedure you came to know how procedure create and run. Now let’s move the parameterized procedure


Parameterized Procedure
The parameter can be both input and output. If you are The programmer then remembers how you create your function in your programming Language.
Yes, same we create here.
Example of Parameterized procedure
Suppose we want to create a procedure for insert in The table. We need data to insert in the table which will be passed to the Procedure through parameters.

create proc Proc_insert1 (@id int, @name varchar (100), @course varchar(100), @fees int)
as
begin
insert into student values(@id, @name, @course, @fees);
end
To Execute this statement
exec Proc_insert 1,'neha', 'php', 6000

Procedure with output parameter
Suppose we want to show the max Salary. If we simply creates procedure to show salary (without output parameter), Then we can not use this output in future. If we want to use this output Outside the procedure, in this case we have to create procedure with out Parameter.

For example:-
create proc porc_maxsal(@sal int out)
as
begin
select @sal=MAX(salary) from Emp;
end

Execution of this procedure
declare @salary int
exec porc_maxsal @salary out;
select @salary
to execute this procedure we first have to declare the output variable which will hold the output. After execution we can use this @salary output in the next query or we can pass it to another procedure too.

Procedure with Return Value
In the above example we pass output parameter to the procedure and hold output in this variable. In the following example we didn’t pass any variable into the procedure. Infact procedure will return the output(as we use function in out programming language like c, java)

For Example:-
create proc porc_MaxSalWithRet
as
begin
declare @sal int
select @sal=MAX(salary) from Emp;
return @sal;
end

Execution of this procedure

declare @salary int
exec @salary=porc_MaxSalWithRet;
select @salary

Calling procdure from another Procedure
As we create porc_MaxSalWithRet above. Now we are crating another procedure which will execute porc_MaxSalWithRet inside it and use its output.

create proc porc_ProcInProc
as
begin
declare @salary int
exec @salary=porc_MaxSalWithRet;

select * from emp where salary=@salary;
end
exec porc_ProcInProc

hope you enjoyed this article. For any query you can mail me at malhotra.isha3388@gmail.com.

Join our Facebook page to get updates of Jobs, Interview Question, Course Details and Study Material


6 comments:

  1. awesome ma'am.... thank you so much... :)

    ReplyDelete
  2. great... update again, i wanna subscribe

    ReplyDelete
  3. Nice Post But i want to share something about Return in procedure

    RETURN Exits unconditionally from a query or procedure. RETURN is immediate and complete and can be used at any point to exit from a procedure, batch, or statement block. Statements that follow RETURN are not executed.

    When used with a stored procedure, RETURN cannot return a null value. If a procedure tries to return a null value (for example, using RETURN @status when @status is NULL), a warning message is generated and a value of 0 is returned.
    The return status value can be included in subsequent Transact-SQL statements in the batch or procedure that executed the current procedure, but it must be entered in the following form: EXECUTE @return_status = .

    Returning from a procedure

    The following example shows if no user name is specified as a parameter when findjobs is executed, RETURN causes the procedure to exit after a message has been sent to the user's screen. If a user name is specified, the names of all objects created by this user in the current database are retrieved from the appropriate system tables.

    CREATE PROCEDURE findjobs @nm sysname = NULL
    AS
    IF @nm IS NULL
    BEGIN
    PRINT 'You must give a user name'
    RETURN
    END
    ELSE
    BEGIN
    SELECT o.name, o.id, o.uid
    FROM sysobjects o INNER JOIN master..syslogins l
    ON o.uid = l.sid
    WHERE l.name = @nm
    END;

    ReplyDelete
    Replies
    1. Yes this is also concept in Stored Procedure.

      Delete