-1

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.

Thom A
  • 88,727
  • 11
  • 45
  • 75
Riccardo
  • 11
  • 2
  • You're adding the same parameter name twice... that doesn't sound like a good idea to me. Why are you doing that? Have you tried without the `cmd.Parameters.AddWithValue("@Stato", -99);` call? – Jon Skeet May 31 '23 at 16:20
  • Try `System.Data.ParameterDirection.Output` (or `InouttOutput'). – Dan Guzman May 31 '23 at 16:20
  • 1
    return values and output parameters are different things – Jamiec May 31 '23 at 16:24
  • Side note: you can simplify to a single line `SET @Stato = ISNULL((SELECT Stato FROM TorbaLabels WHERE ID = @IDTorbaLabel), -1;` – Charlieface May 31 '23 at 16:42

1 Answers1

3

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

retParameter.Direction = System.Data.ParameterDirection.Output;
Marco Augias
  • 46
  • 2
  • ok this works, but I also had to modify according to Jon Skeet's other suggestions, i.e. to delete the line cmd.Parameters.AddWithValue("@Status", -99); – Riccardo Jun 01 '23 at 09:01