从T-SQL存储过程中读取的错误输出参数

huangapple go评论78阅读模式
英文:

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.

但当我尝试通过C#的SqlCommand来读取时,它返回一个错误的值,始终为0(零)。

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

我已经有其他类似的代码,执行相同的操作,但这个不起作用。

Here is my stored procedure:

    (@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:

SET @out = -99

EXEC GetStateTorbaLabel 43, @out OUTPUT

SELECT @out

在SQL Server Management Studio中的测试代码返回了正确的值:

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

C#代码:

Here StatoTorbaLabel has the wrong value of 0.

在这里,StatoTorbaLabel的值是错误的,为0

英文:

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.

答案1

得分: 3

我相信参数必须声明为输出,而不是作为返回值:

retParameter.Direction = System.Data.ParameterDirection.Output;
英文:

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

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

huangapple
  • 本文由 发表于 2023年6月1日 00:18:56
  • 转载请务必保留本文链接:https://go.coder-hub.com/76375521.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定