英文:
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;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论