-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.

4
  • 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? Commented May 31, 2023 at 16:20
  • Try System.Data.ParameterDirection.Output (or `InouttOutput'). Commented May 31, 2023 at 16:20
  • 1
    return values and output parameters are different things Commented May 31, 2023 at 16:24
  • Side note: you can simplify to a single line SET @Stato = ISNULL((SELECT Stato FROM TorbaLabels WHERE ID = @IDTorbaLabel), -1; Commented May 31, 2023 at 16:42

1 Answer 1

3

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

retParameter.Direction = System.Data.ParameterDirection.Output;
Sign up to request clarification or add additional context in comments.

1 Comment

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);

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.