[sql] 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

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

CREATE PROCEDURE GetTaskEvents
@TaskName varchar(50),
@Id INT
AS
BEGIN
-- SP Logic
END

Procedure Calling

DECLARE @return_value nvarchar(50)

EXEC  @return_value = GetTaskEvents
        @TaskName = 'TaskName',
        @Id =2  

SELECT  'Return Value' = @return_value

I'm going on a bit of an assumption here, but I'm assuming the logic inside the procedure gets split up via task. And you cant have nullable parameters as @Yuck suggested because of the dynamics of the parameters?

So going by my assumption

If TaskName = "Path1" Then Something

If TaskName = "Path2" Then Something Else

My initial thought is, if you have separate functions with business-logic you need to create, and you can determine that you have say 5-10 different scenarios, rather write individual stored procedures as needed, instead of trying one huge one solution fits all approach. Might get a bit messy to maintain.

But if you must...

Why not try dynamic SQL, as suggested by @E.J Brennan (Forgive me, i haven't touched SQL in a while so my syntax might be rusty) That being said i don't know if its the best approach, but could this could possibly meet your needs?

CREATE PROCEDURE GetTaskEvents
    @TaskName varchar(50)
    @Values varchar(200)
AS
BEGIN
  DECLARE @SQL VARCHAR(MAX)

  IF @TaskName = 'Something'
  BEGIN
    @SQL = 'INSERT INTO.....' + CHAR(13)
    @SQL += @Values + CHAR(13) 
  END

  IF @TaskName = 'Something Else'
  BEGIN
    @SQL = 'DELETE SOMETHING WHERE' + CHAR(13)
    @SQL += @Values + CHAR(13) 
  END

  PRINT(@SQL)
  EXEC(@SQL)    
END

(The CHAR(13) adds a new line.. an old habbit i picked up somewhere, used to help debugging/reading dynamic procedures when running SQL profiler.)

You are parsing wrong parameter combination.here you passing @TaskName = and @ID instead of @TaskName = .SP need only one parameter.

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