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