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, guide me.