SQL存储过程中的可选参数

Question

我正在尝试创建一个具有可选参数的存储过程。我按照这里列出的说明做了。我也参考了这个问题。然而，我一直收到以下错误:

将数据类型varchar转换为int时出错。

当我以

EXEC sp_get_user {id#}

或

EXEC sp_get_user NULL, {username}

但是

失败

EXEC sp_get_user {username}

存储过程

@id int = NULL,
@username nvarchar(50) = NULL
SELECT
    username = COALESCE(a.Username, b.Username),
    password = COALESCE(a.Password, b.Password),
    signup_date = COALESCE(a.SignedUpOn, b.Signup_Date)
FROM table1 a
FULL OUTER JOIN table 2 b
ON a.ID = b.ID
WHERE ((a.ID = @id OR @id IS NULL)
AND (a.Username = @username OR @username IS NULL)
OR (b.ID = @id OR @id IS NULL)
AND (b.Username = @username OR @username IS NULL))

我已经尝试添加OPTION(RECOMPILE)，没有成功。我想让它是动态的，这样其他开发人员就可以调用这个SP，而不必每次都指定所有参数。如果有区别的话，他们将通过LINQ连接。

Answer 1

在这种情况下使用命名参数

EXEC sp_get_user @username = {username}

如果这两个参数都是可选的，SQL server将按位置排序，因此您传入的第一个参数将映射到进程

Answer 2

在执行存储过程时，您必须遵守分别定义的参数顺序，这就是为什么第一个和第二个语句工作得很好，在第一个EXEC sp_get_user {id#}中，您传递id并忽略用户名，然后它采用定义的默认值。此外，在第二个语句EXEC sp_get_user NULL, {username}中，您为id指定了NULL，并为用户名参数传递了一个值，这就是为什么它也有效。

另一方面，第三个EXEC sp_get_user {username}不工作，因为SQL Server处理您的参数{username}作为id值，这就是为什么它试图将其转换为整数，当然它会失败。相反，您必须在传递其值时指定参数名称，请参阅以下代码:

EXEC sp_get_user @username = {username}

Answer 3

嗯，是的，显然你最后的尝试会失败。

存储过程需要两个参数，顺序如下:

1. @id INT
2. @username NVARCHAR(50)

如果您只是简单地使用单个参数调用存储过程，那么该参数将映射到@id -因此它需要是INT。

如果你想用一个单独的用户名值来调用你的存储过程，你需要使用一个名为的参数——你不能依赖于位置(因为第一个也是唯一一个参数总是与@id匹配)

EXEC sp_get_user @username = {username}