Home > DeveloperSection > Forums > How to create stored procedure with output parameter in sql server
Ankit Singh

Total Post:341

Points:2389
Posted on    March-08-2016 10:48 PM

 Database SQL Server  SQL 
Ratings:


 1 Reply(s)
 316  View(s)
Rate this:
We want to create stored procedure with output parameter in sql server. How to create and use please help me.


aditya kumar Patel

Total Post:250

Points:1782
Posted on    March-08-2016 11:05 PM

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 :

 

 

 

 

 


Don't want to miss updates? Please click the below button!

Follow MindStick