Post reply

Variable Parameter list for for procedure

  • November 6, 2012 at 4:46 am

    #266586

    Dear All

    I need to create procedure with variable parmaeter list. I am trying to achive it through table value parameter.

    Is it the correct approch?

    Also I need this parameter to be optional. But when i give folloing syntax i get error

    CREATE PROCEDURE [P1]

    @param1 VARCHAR(40),

    @ParameterTable ParameterTable READONLY = NULL

    as

    .....

  • November 6, 2012 at 5:01 am

    #1556403

    You can use default Stored Procedure Template from Template Explorer.

    it will help you

    SSMS > View > Template Explorer > Stored Procedure

    -----------------------------------------------------------------------------
    संकेत कोकणे

  • November 6, 2012 at 6:03 am

    #1556423

    shilpaprele (11/6/2012)

    Dear All

    I need to create procedure with variable parmaeter list. I am trying to achive it through table value parameter.

    Is it the correct approch?

    Also I need this parameter to be optional. But when i give folloing syntax i get error

    CREATE PROCEDURE [P1]

    @param1 VARCHAR(40),

    @ParameterTable ParameterTable READONLY = NULL

    as

    .....

    Table-valued parameter is the best for passing table-like data, but not variable parameter list.

    Let say you want to pass multiple Customer Id's together with relevant Customer Names. Table-valued parameter is upto this task.

    But if you want to pass list of different parameters eg. not-related to each other and having different datatype, I would recommend using XML.

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • November 6, 2012 at 6:52 am

    #1556441

    I checked default template does not cater to my changing parameter reuirement. Also it is not showing me how to make table type paramter as optional

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply