CODEWITHSUNDEEP
c#sqlt-sqlstored-proceduresoutput-parameter
Stefan Steiger
Stefan Steiger

Reputation: 82146

C#: Retrieve output parameter of non-parametrized stored procedure query?

I have this stored procedure

CREATE PROCEDURE [dbo].[TestProcedure] 
     @param1 int = 0
    ,@param2 int = 0
    ,@total_sales int = 5 OUTPUT 
AS
BEGIN
    SET NOCOUNT ON;

    SET @total_sales = @total_sales * 5
    SELECT * FROM SomeTable
END

And this string in C#

string strSQL = @"
                DECLARE @RC int
                DECLARE @param1 int
                DECLARE @param2 int
                DECLARE @total_sales int

                -- TODO: Set parameter values here.
                SET @param1 = 1
                SET @param2 = 2

                EXECUTE @RC = [TestDB].[dbo].[TestProcedure] 
                   @param1
                  ,@param2
                  ,@total_sales OUTPUT";

And now I want to retrieve the output value, but without parametrizing the input query !

I tried this:

using (System.Data.SqlClient.SqlCommand cmd = (System.Data.SqlClient.SqlCommand)idbConn.CreateCommand())
{
   cmd.CommandText = strSQL;
   cmd.Transaction = (System.Data.SqlClient.SqlTransaction)idbtTrans;

   iAffected = cmd.ExecuteNonQuery();
   idbtTrans.Commit();

   string strOutputParameter = cmd.Parameters["@total_sales"].Value.ToString();
   Console.WriteLine(strOutputParameter);
} // End Using IDbCommand

And this throws an exception (the parameter @total_sales is not in the parameter list).

How can I retrieve an output parameter in a non-parametrized stored-procedure call WITHOUT parametrizing the query ?

Upvotes: 1

Views: 1008

Answers (2)

Moe Sisko
Moe Sisko

Reputation: 12005

Well, not sure if this will help, but the following is technically possible :

In your TSQL scripts, use RAISERROR to raise informational messages. You could use this to return information about the name and value of variables. e.g.

DECLARE @Msg NVARCHAR(200)
SET @Msg = 'totalsales=5' -- construct string of form : VariableName=ValueAsString
RAISERROR(@Msg, 10, 1)

In C#, use the SqlConnection.InfoMessage event to catch these messages. Parse the returned strings to extract the names and values. It would be a roundabout way to return parameter values, but it would work.

Upvotes: 1

Jamiec
Jamiec

Reputation: 136074

Short answer: You can't.

Long Answer: You need to use ADO.NET in a more standard way to enable you to leverage things such as output parameters.

Upvotes: 6

Related Questions