SQL Server stored procedure parameters

Question

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

Answer 1

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:

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 use in 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 

Answer 2

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.