在Management Studio中的存储过程正常运行时，Command.ExecuteScalar始终返回null [英] Command.ExecuteScalar always return null while Stored Procedure in Management Studio runs fine

问题描述

我有以下带有一个输入参数和一个out参数的SQL存储过程.

I have the following SQL stored procedure with one input parameter and one out parameter.

CREATE PROCEDURE [dbo].[spCanUserEdit]
(
@username nvarchar(255)
)
AS
BEGIN
SET NOCOUNT ON;

DECLARE @CanEdit bit

SELECT
    @CanEdit = CanUserEdit
FROM tblUsers
WHERE username = LOWER(@username)
RETURN SELECT @CanEdit
END
GO

在 tblUsers 中 CanUserEdit 列上方的存储过程中，是位类型列，默认值为0.现在，当我在Management Studio中执行此过程时，它可以正常运行，但是当我在C#代码中使用 command.ExecuteScalar()时，它总是返回 null .谁能告诉我我在做什么错了.

In the stored procedure above CanUserEdit column in tblUsers is bit type column and with default value to 0. Now when I execute this procedure in Management Studio it runs fine but when i use command.ExecuteScalar() in my C# code, it always returns null. Could anyone please tell me what I am doing wrong here.

以下是我的C#方法

public static bool CanUserEdit(string userName)
{
    using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings[Constants.ConnectionStringName].ConnectionString))
    {
        using (SqlCommand cmd = new SqlCommand())
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "spCanUserEdit";
            cmd.Connection = conn;

            cmd.Parameters.Add(new SqlParameter("@username", userName));

            conn.Open();
            bool canEdit = (bool)cmd.ExecuteScalar();

            return canEdit;
        }
    }
}

亲切的问候

推荐答案

问题在于您返回数据的方式.如果要使用ExecuteScalar，则不应返回，而应选择SELECT.

The problem is in the way you return data. If you want to use ExecuteScalar, you should not RETURN but instead simply SELECT.

尝试如下更改SP:

CREATE PROCEDURE [dbo].[spCanUserEdit]
(
 @username nvarchar(255)
)
AS
BEGIN
SET NOCOUNT ON;

DECLARE @CanEdit bit

SELECT
    @CanEdit = CanUserEdit
FROM tblUsers
WHERE username = LOWER(@username)

SELECT @CanEdit

RETURN 0
END
GO

如果您不能更改SP，而只能更改代码，则解决方案是使用ExecuteNonQuery读取参数'@ReturnValue'.

If you can't change the SP, but the code, the solution is to read parameter '@ReturnValue' with ExecuteNonQuery.

这篇关于在Management Studio中的存储过程正常运行时，Command.ExecuteScalar始终返回null的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！