---
title: "How to create stored procedure with output parameter in sql server"  
description: "How to create stored procedure with output parameter in sql server"  
author: "Anonymous User"  
published: 2016-03-08  
updated: 2016-03-08  
canonical: https://www.mindstick.com/forum/34042/how-to-create-stored-procedure-with-output-parameter-in-sql-server  
category: "database"  
tags: ["sql server", "sql"]  
reading_time: 2 minutes  

---

# How to create stored procedure with output parameter in sql server

We want to create [stored procedure](https://www.mindstick.com/articles/803/using-stored-procedure-in-asp-dot-net) with [output](https://www.mindstick.com/interview/34427/explain-the-output-in-angular) [parameter](https://www.mindstick.com/blog/450/parameter-class-in-c-sharp) in [sql server](https://www.mindstick.com/articles/12999/what-is-table-valued-function-in-sql-server). How to create and use please help me.

## Replies

### Reply by Anonymous User

**Input And Output parameter [stored](https://www.mindstick.com/forum/157561/what-is-the-stored-procedure-create-a-procedure-to-find-the-record-by-stu_id-from-the-student-table) [procedure](https://www.mindstick.com/forum/32/stored-procedure-return-datatype)**

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)  OUTPUTAS 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 :    
```


---

Original Source: https://www.mindstick.com/forum/34042/how-to-create-stored-procedure-with-output-parameter-in-sql-server

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.