英文:
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)ASSELECT @Stato = StatoFROM TorbaLabelsWHERE ID = @IDTorbaLabelSET @Stato = IIF(@Stato IS NULL, -1, @Stato)
这是我的存储过程：
The testing code in SQL Server Management Studio that returns the correct value:
SET @out = -99EXEC GetStateTorbaLabel 43, @out OUTPUTSELECT @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)ASSELECT @Stato = StatoFROM TorbaLabelsWHERE ID = @IDTorbaLabelSET @Stato = IIF(@Stato IS NULL, -1, @Stato)
The testing code in SQL Server Management Studio that returns the correct value:
DECLARE @out IntSET @out = -99EXEC GetStateTorbaLabel 43, @out OUTPUTSELECT @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;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论