SQL Server 存储过程参数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15191517/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server stored procedure parameters
提问by Narayan Akhade
I am developing a framework, where in I am a calling stored procedure with dynamically created parameters. I am building parameter collection at the runtime.
我正在开发一个框架，其中我是一个带有动态创建参数的调用存储过程。我正在运行时构建参数集合。
The problem occurs when I am passing a parameter to stored procedure, but stored proc doesn't accept such parameter.
当我将参数传递给存储过程时会出现问题，但存储过程不接受此类参数。
For example, my stored procedure is:
例如，我的存储过程是：
CREATE PROCEDURE GetTaskEvents
@TaskName varchar(50)
AS
BEGIN
-- SP Logic
END
Calling stored procedure as:
调用存储过程为：
EXEC GetTaskEvents @TaskName = 'TESTTASK', @ID = 2
This throws below error:
这会引发以下错误：
Msg 8144, Level 16, State 2, Procedure GetTaskEvents, Line 0
Procedure or function GetTaskEvents has too many arguments specified.
This works fine in Sybase ASE, which simply ignores any additional parameters. Could this be achieved with MSSQL server 2008? Any help, much appreciated. Thanks
这在 Sybase ASE 中工作正常，它只是忽略任何附加参数。这可以通过 MSSQL Server 2008 实现吗？任何帮助，非常感谢。谢谢
采纳答案by E.J. Brennan
Why would you pass a parameter to a stored procedure that doesn't use it?
为什么要将参数传递给不使用它的存储过程？
It sounds to me like you might be better of building dynamic SQL statements and then executing them. What you are trying to do with the SP won't work, and even if you could change what you are doing in such a way to accommodate varying numbers of parameters, you would then essentially be using dynamically generated SQL you are defeating the purpose of having/using a SP in the first place. SP's have a role, but there are not the solution in all cases.
在我看来，构建动态 SQL 语句然后执行它们可能会更好。你试图用 SP 做的事情是行不通的，即使你可以改变你正在做的事情以适应不同数量的参数，那么你本质上是在使用动态生成的 SQL，你违背了目的首先拥有/使用SP。SP 有一定的作用，但并非在所有情况下都有解决方案。
回答by Yuck
SQL Server doesn't allow you to pass parameters to a procedure that you haven't defined. I think the closest you can get to this sort of design is to use optional parameters like so:
SQL Server 不允许您将参数传递给您尚未定义的过程。我认为最接近这种设计的是使用可选参数，如下所示：
CREATE PROCEDURE GetTaskEvents
@TaskName varchar(50),
@ID int = NULL
AS
BEGIN
-- SP Logic
END;
You would need to include every possible parameter that you might usein the definition. Then you'd be free to call the procedure either way:
您需要包括您可能在定义中使用的每个可能的参数。然后，您可以随意调用该过程：
EXEC GetTaskEvents @TaskName = 'TESTTASK', @ID = 2;
EXEC GetTaskEvents @TaskName = 'TESTTASK'; -- @ID gets NULL here
回答by Rohan Büchner
I'm going on a bit of an assumption here, but I'm assuming the logic inside the procedure gets split up via task. And you cant have nullable parameters as @Yucksuggested because of the dynamics of the parameters?
我在这里做一些假设，但我假设程序内部的逻辑通过任务拆分。由于参数的动态性，您不能像@Yuck建议的那样使用可为空的参数？
So going by my assumption
所以按照我的假设
If TaskName = "Path1" Then Something
If TaskName = "Path2" Then Something Else
如果 TaskName = "Path1" 然后某事
如果 TaskName = "Path2" 然后是别的
My initial thought is, if you have separate functions with business-logic you need to create, and you can determine that you have say 5-10 different scenarios, rather write individual stored procedures as needed, instead of trying one huge one solution fits all approach. Might get a bit messy to maintain.
我最初的想法是，如果您需要创建具有业务逻辑的单独功能，并且您可以确定您有 5-10 个不同的场景，而是根据需要编写单独的存储过程，而不是尝试一个适合所有人的庞大解决方案方法。维护起来可能有点麻烦。
But if you must...
但如果你必须...
Why not try dynamic SQL, as suggested by @E.J Brennan(Forgive me, i haven't touched SQL in a while so my syntax might be rusty) That being said i don't know if its the best approach, but could this could possibly meet your needs?
为什么不尝试动态 SQL，正如@EJ Brennan所建议的那样（原谅我，我有一段时间没有接触 SQL，所以我的语法可能生疏了）话虽如此，我不知道它是否是最好的方法，但这可以吗？可能满足您的需求？
CREATE PROCEDURE GetTaskEvents
@TaskName varchar(50)
@Values varchar(200)
AS
BEGIN
DECLARE @SQL VARCHAR(MAX)
IF @TaskName = 'Something'
BEGIN
@SQL = 'INSERT INTO.....' + CHAR(13)
@SQL += @Values + CHAR(13)
END
IF @TaskName = 'Something Else'
BEGIN
@SQL = 'DELETE SOMETHING WHERE' + CHAR(13)
@SQL += @Values + CHAR(13)
END
PRINT(@SQL)
EXEC(@SQL)
END
(The CHAR(13) adds a new line.. an old habbit i picked up somewhere, used to help debugging/reading dynamic procedures when running SQL profiler.)
（CHAR(13) 添加了一个新行……我在某处捡到的一个旧习惯，用于在运行 SQL 分析器时帮助调试/读取动态过程。）
回答by Ad Kahn
CREATE PROCEDURE GetTaskEvents
@TaskName varchar(50),
@Id INT
AS
BEGIN
-- SP Logic
END
Procedure Calling
过程调用
DECLARE @return_value nvarchar(50)
EXEC @return_value = GetTaskEvents
@TaskName = 'TaskName',
@Id =2
SELECT 'Return Value' = @return_value
回答by CodeMind
You are parsing wrong parameter combination.here you passing @TaskName =and @IDinstead of @TaskName =.SP need only one parameter.
您正在解析错误的参数组合。在这里您传递@TaskName =而@ID不是@TaskName =.SP 只需要一个参数。
