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.

比如我的存储过程是:

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 有作用，但不是所有情况下都有解决方案.

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屋！