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

User · Accepted Answer

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.

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql] SQL Server stored procedure parameters

Examples related to sql

Examples related to sql-server-2008

Examples related to stored-procedures

Examples related to optional-parameters