Post reply

Pass Table type Output parameter in SQL2000

  • November 12, 2007 at 11:36 pm

    #61403

    Dear all,

    I have following doubts ,

    1) Can we pass a table name to procedure in sql2000? if yes please give me example

    2) Can I return a table type output parameter from a procedure in Sql2000?

    thanks in advance

    Stephen

  • November 13, 2007 at 12:09 am

    #750484

    1. Yes... you would need to use it in dynamic SQL within the sproc, though.

    2. No, but you can from a User Defined Function... (table variable, that is).

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • November 13, 2007 at 12:30 am

    #750491

    Thanks ,

    1) Without using dynamic SQL is it possible like passing it has sysvariable.

    2) Is it possible to return a table output parameter from a procedure in SQL2005 or in higher version

    Thanks again

  • November 13, 2007 at 12:34 am

    #750493

    1. Pretty sure the answer is "No".

    2. I guess I'm not sure what you mean anymore... you can get a result set from a sproc and use it as if it were a table...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • November 13, 2007 at 12:39 am

    #750496

    my aim is to avoid passing too many output paramerters since I have around 250 columns.

    Scenarios is like this I will have a main procedure like Proc1 inside that I will have sub procedures like Proc 2 and Proc 3 which can return around 15 columns details as output.If I can get it has table output then it is not required for me to pass all 15 variable s as output parameter. I can use table variable through out the main procedure (PROC1) to refer the values.

    I cannot use functions since it has impact on performace.

  • November 13, 2007 at 12:57 am

    #750503

    How many rows do you intend to return with 250 columns?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • November 13, 2007 at 1:00 am

    #750504

    not more than 4 rows

  • November 13, 2007 at 1:13 am

    #750506

    Then I'd use a function with a table variable... not gonna hurt performance any more than a stored proc.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • November 13, 2007 at 1:16 am

    #750509

    I understand your point but my seniors wants it to be as sp's.

    Their point is that function used recompile each everytime it is executed.So if SQL2000 doesn't support explicit table output parameter then my life will be easier.

  • November 13, 2007 at 5:37 am

    #750606

    I won't pass a table variable... best you can do is a result set that get's used as a table.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 10 posts - 1 through 9 (of 9 total)

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