CODEWITHSUNDEEP
c#sql-serverstored-proceduressqlcommand
Riccardo
Riccardo

Reputation: 11

Wrong output parameter read from T-SQL stored procedure

I have to read a value from a simple T-SQL stored procedure. When I test the procedure in SQL, it works fine.

But when I try to read by a C# SqlCommand, it returns a wrong value that is always 0 (zero).

I have already other similar code that do the same thing but this one doesn't works.

Here is my stored procedure:

CREATE PROCEDURE GetStateTorbaLabel 
    (@IDTorbaLabel Int, 
     @Stato Int Output) 
AS
    SELECT @Stato = Stato 
    FROM TorbaLabels 
    WHERE ID = @IDTorbaLabel

    SET @Stato = IIF(@Stato IS NULL, -1, @Stato)

The testing code in SQL Server Management Studio that returns the correct value:

DECLARE @out Int
SET @out = -99

EXEC GetStateTorbaLabel 43, @out OUTPUT

SELECT @out

The C# code:

SqlCommand cmd = new SqlCommand("GetStateTorbaLabel", connection);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@IDTorbaLabel", 43);

SqlParameter retParameter = cmd.Parameters.Add("@Stato", System.Data.SqlDbType.Int);
retParameter.Direction = System.Data.ParameterDirection.ReturnValue;

cmd.Parameters.AddWithValue("@Stato", -99);

cmd.ExecuteNonQuery();

StatoTorbaLabel = Convert.ToInt32(retParameter.Value);

Here StatoTorbaLabel has the wrong value of 0.

Upvotes: -1

Views: 125

Answers (1)

Marco Augias
Marco Augias

Reputation: 46

I believe the parameter must be declared as output, not as return value:

retParameter.Direction = System.Data.ParameterDirection.Output;

Upvotes: 3

Related Questions