locked
Proc params with defualt values RRS feed

  • Question

  • Default parameters, I am calling an existing proc and passing my parameters to it.  If they add a param with a default value at the end of the params list I want to be sure that that wont brake me proc?  Is there any case where that would happen?

    Create Proc [dbo].[MyProc](
     @A int,
     @B datetime,
     @C bit
     
    )
    as
    Begin

     Insert into #T
      EXECUTE  [herProc]
       @A, @B, @C

     Select ... from #t
     inner  join ...
     where ...
    End

    Create Proc [dbo].[herProc](
     @A int,
     @B datetime,
     @C bit,
     @D.....
     
    )
    as
    Begin

     Select .....
    End


    M~
    Tuesday, November 1, 2011 9:25 PM

Answers

  • Check this link: http://msdn.microsoft.com/en-us/library/ms187926.aspx

    USE AdventureWorks2008R2;
    GO
    IF OBJECT_ID ( 'HumanResources.uspGetEmployees', 'P' ) IS NOT NULL
        DROP PROCEDURE HumanResources.uspGetEmployees;
    GO
    CREATE PROCEDURE HumanResources.uspGetEmployees
        @LastName nvarchar(50),
        @FirstName nvarchar(50)
    AS

        SET NOCOUNT ON;
        SELECT FirstName, LastName,Department
        FROM HumanResources.vEmployeeDepartmentHistory
        WHERE FirstName = @FirstName AND LastName = @LastName;
    GO

    EXECUTE HumanResources.uspGetEmployees N'Ackerman', N'Pilar';
    -- Or
    EXEC HumanResources.uspGetEmployees @LastName = N'Ackerman', @FirstName = N'Pilar';
    GO
    -- Or
    EXECUTE HumanResources.uspGetEmployees @FirstName = N'Pilar', @LastName = N'Ackerman';
    GO
    -- Or, if this procedure is the first statement within a batch:
    HumanResources.uspGetEmployees N'Ackerman', N'Pilar';

     


    Best Wishes, Arbi --- MCC 2011; Please vote if you find this posting was helpful or Mark it as answered.
    • Marked as answer by KJian_ Wednesday, November 9, 2011 7:27 AM
    Wednesday, November 2, 2011 4:17 PM

All replies

  • As long as SECOND stored procedure in this case "HERPROC" has default value for added paramters at the END you will be fine.

    But if they add a parameter in between and also have default value you will be in trouble.

     For example: if they add a new parameter beween param 1 and 2 and lets say "@A_1 Char(1) = 'Y'", now this parameter become second in the list of paramters and in your procedure you are passing DATETIME value to second param ("@B") which will cause error in this case.


    Best Wishes, Arbi --- MCC 2011; Please vote if you find this posting was helpful or Mark it as answered.
    Tuesday, November 1, 2011 9:33 PM
  • Hi Mw !

    I think you code looks fine, if you call usp 'herProc' from usp 'MyProc' and only provide 3 parameters while 'herProc' is expecting 4 param values but it will fill the unprovided last param value with Default Value.

    Thanks, Hasham

    Tuesday, November 1, 2011 9:34 PM
    Answerer
  • How would i pass the params by name?
    M~
    Wednesday, November 2, 2011 12:15 PM
  • Check this link: http://msdn.microsoft.com/en-us/library/ms187926.aspx

    USE AdventureWorks2008R2;
    GO
    IF OBJECT_ID ( 'HumanResources.uspGetEmployees', 'P' ) IS NOT NULL
        DROP PROCEDURE HumanResources.uspGetEmployees;
    GO
    CREATE PROCEDURE HumanResources.uspGetEmployees
        @LastName nvarchar(50),
        @FirstName nvarchar(50)
    AS

        SET NOCOUNT ON;
        SELECT FirstName, LastName,Department
        FROM HumanResources.vEmployeeDepartmentHistory
        WHERE FirstName = @FirstName AND LastName = @LastName;
    GO

    EXECUTE HumanResources.uspGetEmployees N'Ackerman', N'Pilar';
    -- Or
    EXEC HumanResources.uspGetEmployees @LastName = N'Ackerman', @FirstName = N'Pilar';
    GO
    -- Or
    EXECUTE HumanResources.uspGetEmployees @FirstName = N'Pilar', @LastName = N'Ackerman';
    GO
    -- Or, if this procedure is the first statement within a batch:
    HumanResources.uspGetEmployees N'Ackerman', N'Pilar';

     


    Best Wishes, Arbi --- MCC 2011; Please vote if you find this posting was helpful or Mark it as answered.
    • Marked as answer by KJian_ Wednesday, November 9, 2011 7:27 AM
    Wednesday, November 2, 2011 4:17 PM