Stored Procedure with Output Perameters

Sql Server provides the most effective way to access database through Stored Procedures. Stored Proc perform tasks in very optimize manner as it can executes more than one sql quries, which save us to call database again and again. By this Programmability in SQL Server , one can perform all data manupalation tasks like Insert , Update , delete records.

Here i am trying to write a stored procedure that can take product id as input parameter and return product name as output perameter.

Create PROC sp_getProductName
@pid int ,
@pName nvarchar(50)OUTPUT
Select @pName=ProductName from Products where porductID=@pid

To check if it is working fine, we need to Execute it. The following commands shows how we can execute strored procedures having parameters of both type input and output.

Declare @pName nvarchar(50)
Exec sp_getProductName
@pName=@pName OUTPUT

select @pName as 'Product name'

The result in SQL Server 2005 will be look like: