Returning Output from a Stored Procedure – Microsoft SQL Server

As mentioned in the below article Microsoft® SQL Server Stored Procedures can return data in four different ways.

 http://technet.microsoft.com/en-us/library/ms188655(v=sql.105).aspx 

The below example shows you how to get an output based on the input supplied to a stored procedure.

Create PROCEDURE Test_SP_Output

@Param_ID as NVARCHAR(15),
@Param_ColumnName as NVARCHAR(100),
@OutputValue NVARCHAR(255) OUTPUT

AS
BEGIN

SET NOCOUNT ON;

DECLARE @sql NVARCHAR(512)

BEGIN TRY

SET @sql = N’SELECT @OutputValue = ‘+@Param_ColumnName+’ FROM ‘
+ ‘Table_Name where ID = ”’ + @Param_ID +””

EXEC sp_executesql
@query = @sql,
@params = N’@OutputValue NVARCHAR(255) OUTPUT’,
@OutputValue = @OutputValue OUTPUT

RETURN @OutputValue

END TRY

BEGIN CATCH

RETURN null
PRINT ‘Error : ‘ + CAST(@@ERROR AS VARCHAR(20))
PRINT ‘Error State : ‘ + CAST(ERROR_STATE() AS VARCHAR(20))
PRINT ‘Error Message : ‘ + ERROR_MESSAGE()

END CATCH

END

 

To call the above procedure:


DECLARE @Out NVARCHAR(255)
EXEC Test_SP_Output '6184','Product', @OutputValue=@Out OUTPUT
select @Out as Value

If there is a better way to accomplish this please guide me / comment here.

Advertisements

About Joseph Velliah
As a SharePoint Developer my professional interests tend to be technical and SharePoint focused. I run a blog at "SP RIDER" where you can expect to read HOW TOs and scenarios that I run into during my day to day job. I hope my posts will give back a little to the community that is helped me.

Comments are closed.

%d bloggers like this: