SQL Server存储过程参数 [英] SQL Server stored procedure parameters

问题描述

我正在开发一个框架，其中是一个带有动态创建参数的调用存储过程.我在运行时建立参数集合.

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

调用存储过程为:

EXEC GetTaskEvents @TaskName = 'TESTTASK', @ID = 2

这引发以下错误:

Msg 8144, Level 16, State 2, Procedure GetTaskEvents, Line 0
Procedure or function GetTaskEvents has too many arguments specified.

这在Sybase ASE中工作正常，它仅忽略任何其他参数.可以使用MSSQL Server 2008来实现吗?任何帮助，不胜感激.谢谢

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

推荐答案

为什么要将参数传递给不使用该参数的存储过程?

Why would you pass a parameter to a stored procedure that doesn't use it?

在我看来，您可能最好先构建动态SQL语句然后执行它们.您尝试使用SP进行的操作将无法正常工作，即使您可以通过改变方式来适应各种数量的参数，但实际上使用的是动态生成的SQL，这使您无法实现SP的目的.首先具有/使用SP. SP可以发挥作用，但并非在所有情况下都可以提供解决方案.

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 Server存储过程参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！