You must Sign In to post a response.
  • Category: SQL Server

    stored procedure example

    hi..
    give a sample program example on writing stored procedure in sql server..


    thanks in advance
  • #335060
    Hi,

    Let's take a look at a practical example. Assume we have the table shown at the bottom of this page, named Inventory. This information is updated in real-time and warehouse managers are constantly checking the levels of products stored at their warehouse and available for shipment. In the past, each manager would run queries similar to the following:



    SELECT Product, Quantity
    FROM Inventory
    WHERE Warehouse = 'FL'



    This resulted in very inefficient performance at the SQL Server. Each time a warehouse manager executed the query, the database server was forced to recompile the query and execute it from scratch. It also required the warehouse manager to have knowledge of SQL and appropriate permissions to access the table information.

    We can simplify this process through the use of a stored procedure. Let's create a procedure called sp_GetInventory that retrieves the inventory levels for a given warehouse. Here's the SQL code:



    CREATE PROCEDURE sp_GetInventory
    @location varchar(10)
    AS
    SELECT Product, Quantity
    FROM Inventory
    WHERE Warehouse = @location


    Our Florida warehouse manager can then access inventory levels by issuing the command

    EXECUTE sp_GetInventory 'FL'

    The New York warehouse manager can use the same stored procedure to access that area's inventory.

    EXECUTE sp_GetInventory 'NY'




    Granted, this is a simple example, but the benefits of abstraction can be seen here. The warehouse manager does not need to understand SQL or the inner workings of the procedure. From a performance perspective, the stored procedure will work wonders. The SQL Sever creates an execution plan once and then reutilizes it by plugging in the appropriate parameters at execution time.

    Now that you've learned the benefits of stored procedures, get out there and use them! Try a few examples and measure the performance enhancements achieved -- you'll be amazed!

    Inventory Table:

    ID Product Warehouse Quantity

    142 Green beans NY 100
    214 Peas FL 200
    825 Corn NY 140
    512 Lima beans NY 180
    491 Tomatoes FL 80
    379 Watermelon FL 85


    Hope it Helps!

    Thanks and Regards,
    Babu Akkandi

  • #335061
    Hi,

    Also Refer the Documentation of MSDN, Titled as "SQL Stored Procedures" from

    http://msdn.microsoft.com/en-us/library/aa174792.aspx

    Here, explained what is SP? and Types of SP? and sample SP are available.

    Hope it Helps!

    Thanks and Regards,
    Babu Akkandi

  • #335085
    The code sample is a SQL Stored Procedure named prcDeductions that calculates the PF and PT of an employee. The employee is identified by his Employee Number.

    Both PF and PT are OUTPUT variables here.


    CREATE PROCEDURE prcDeductions(@Empno int,@PF money OUTPUT,@PT money OUTPUT)
    As
    Begin
    Declare @Sal money
    SELECT @Sal=sal from Emp
    Where empno=@Empno
    SET @PF=@Sal*0.12
    SET @PT=@Sal*0.05
    END



    The below code is an example on how to execute the above procedure. Here, the output variables are passed along with the empno.

    The SQL Procedure is executed with 'EXEC' clause.


    Declare @VPF money,@VPT money
    EXEC prcDeductions 1005,@VPF OUTPUT,@VPT OUTPUT
    PRINT @VPF
    PRINT @VPT


    Thanks & Regards


    Bunty

  • #335115
    stored procedure is advanced feature in sql server that offers you to create,compile and run sql commands in the server itself.
    There are many types of stored procedure availabe in Sql.

    1)User-defined Stored Procedures
    There are several advantages in writing our own stored procedures. We write complex and nested statements with less effort.

    2)Extended Stored Procedures
    Extended stored procedures are used to access SQL server by using dlls.

    3)System Stored Procedures
    System stored procedures are used for administrating, configuring and monitoring the SQL server.


    Ex:-

    select * from Emp


    alter procedure Empp @eusername nvarchar(20),@epassword nvarchar(20),@EEmpid nvarchar(20)
    as
    begin
    insert into Emp (UserName,PassWord,EmpID) values(@eusername,@epassword,@EEmpid)
    end

    alter procedure EmpUpdate @eEmpID nvarchar(20),@eUserName nvarchar(20),@ePassword nvarchar(20)
    as
    begin
    update Emp set PassWord=@ePassword,UserName=@eUserName where EmpID=@eEmpID
    end
    output:
    -------
    empp 'Rengan','Nathan','1050'
    empp 'ravi','ravi','890'
    EmpUpdate 'gr','mahagr','anuja'

    G.Rajan
    Happy New Year


  • This thread is locked for new responses. Please post your comments and questions as a separate thread.
    If required, refer to the URL of this page in your new post.