HOW TO CREATE STORED PROCEDURE WITH OUTPUT PARAMETER IN SQL SERVER

Ankit Singh

Total Post:341

Points:2389
Posted by  Ankit Singh
 613  View(s)
Ratings:
Rate this:
We want to create stored procedure with output parameter in sql server. How to create and use please help me.
  1. aditya kumar Patel

    Post:254

    Points:1810
    Re: How to create stored procedure with output parameter in sql server

    Input And Output parameter stored procedure

    The following example shows a stored procedure with an input and an output parameter. The first parameter in the stored procedure @ProductId receives the input value specified by the calling program, and the second parameter @ProductName is used to return the value to the calling program. The SELECT statement uses the @ProductId parameter to obtain the correct ProductName value, and assigns the value to the @ProductName output parameter.

    CREATE TABLE ProductStock

    (

    ProductId INT PRIMARY KEY IDENTITY(1,1),

    ProductName VARCHAR(100),

    ManufacturedBy VARCHAR(100),

    StockQty BIGINT,

    LastUpdated date

    )

     

    GO

     

    SELECT ProductId,ProductName,ManufacturedBy FROM ProductStock

     

    Create Procedure

     

    -- =============================================

     

     

     

    -- Example to Create the stored procedure

    -- =============================================

     

     

    IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'Sp_GetProductName')

    DROP PROCEDURE Sp_GetProductName

    GO

     

     

    CREATE PROCEDURE Sp_GetProductName

    @ProductId int,

    @ProductName varchar(100) OUTPUT

    AS

     

    SELECT @ProductName =(Select ProductName from ProductStock where ProductId=@ProductId)

     

    GO

     

     

     

     

     

     

    -- =============================================

    -- Example to execute the stored procedure

    -- =============================================

     

     

    DECLARE @ProductName varchar(100);

    EXECUTE Sp_GetProductName 1,@ProductName OUTPUT

    SELECT @ProductName as ProductName

    GO

     

    Result :

     

     

     

     

     

Answer

NEWSLETTER

Enter your email address here always to be updated. We promise not to spam!