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
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.