Executescalar从storedprocedure接收错误的值 [英] Executescalar receives wrong value from storedprocedure

问题描述

我尝试使用executioncalar方法将单个值返回给c＃。当在sql server中执行下面的存储过程时，if..blocks工作正常但在c＃中执行标量总是返回0.



请参考下面的代码

I try to return single value to c# using executescalar method. When execute below stored procedure in sql server, if..blocks are working fine but executescalar in c# always returns 0.

please refer below code

USE [xx]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[prcAddress]
	@ID int, 
	@Name varchar(50),
	@Designation varchar(50)
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @count as Integer -- To count records
	Declare @Result int -- To return result

	SELECT @Result=0
	SELECT @count = (SELECT count(*) FROM dbo.Address)

	IF @ID >0
	BEGIN
	--Update the current entry
	SELECT @Result=1
	END
	ELSE IF @ID =0 AND @count=0
	BEGIN
		-----do something
	SELECT @Result=2
	END
	ELSE IF @ID=0 AND @count>0
	BEGIN
	----do something
	SELECT @Result=3
	END
	ELSE
	BEGIN
	SELECT @Result=4
	END

	SELECT @Result As Result
END
GO

SqlConnection sqlCon = new SqlConnection(ConnectionString);
 SqlCommand sqlCom = new SqlCommand();

 try
 {
     sqlCom = new SqlCommand("prcAddress", sqlCon);
     sqlCom.CommandType = CommandType.StoredProcedure;
     sqlCom.CommandTimeout = 15;
     sqlCom.Connection = sqlCon;
     foreach (KeyValuePair<Object, Object> parmater in parameters)
     {
         if (parmater.GetType() == typeof(DateTime))
         {
             sqlCom.Parameters.Add("@" + parmater.Key, SqlDbType.DateTime).Value = parmater.Value;
         }
         else
         {
             sqlCom.Parameters.AddWithValue("@" + parmater.Key, parmater.Value);
         }
     }

     if (sqlCon.State != ConnectionState.Closed)
     {
         sqlCon.Close();
     }
     sqlCon.Open();
     if (sqlCom.ExecuteScalar() != null)
     {
         result = sqlCom.ExecuteScalar().ToString();
     }
     else
     {
         result = "";
     }
 }
 catch (SqlException sqlEx)
 {
     System.Web.HttpContext.Current.Response.Redirect("~/Error.aspx", false);
 }
 finally
 {
     sqlCon.Close();
     sqlCom = null;
 }

推荐答案

只需在结尾处返回值。替换

Just RETURN the value at the end. Replace

SELECT @Result As Result

使用

With

RETURN @Result

这篇关于Executescalar从storedprocedure接收错误的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！