Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2003
    Location
    France
    Posts
    21

    Unanswered: union with stored procedures

    I need to get the union of 2 stored procedure in a third one.
    Is it possible to do something like:

    exec sp1 @param1
    union
    exec sp2 @param2


    I would like to avoid the use of temp table if possible

    thanks

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1

    Use table functions instead

    Convert your stored procedures to User-defined table functions. Table functions can accept parameters, and return datasets that can be used in most (if not all) standard sql query syntax. When you call them, you must specify the function owner, and you should assign an alias:

    select a, b, c
    from owner.custom_functionA(@paramaeter) customA
    union
    select a, b, c
    from owner.custom_functionB(@paramaeter) customB

    Also, don't forget that in SQL 2000 you can use table variables instead of temporary tables. They are more efficient since data is not written to disk, and they clean up automatically by going out of scope when the procedure is finished.

    User-defined functions are the best thing to come along in SQL server in years. I use them all the time.

    blindman

  3. #3
    Join Date
    Mar 2003
    Location
    France
    Posts
    21
    thanks a lot !
    you save my day !!

  4. #4
    Join Date
    Mar 2003
    Location
    France
    Posts
    21

    Re: Use table functions instead

    Well, I still have some problems because I'm using an exec statement to get my data and I have an error saying that exec statement cannot be used as a source for inserting data in a table variable !

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You can insert data into a table variable using the same syntax you would for a normal table:

    Insert into @Table_Variable select a, b, c from Source_Table

    Rewrite your procedures as table functions.

    The thing you you need to watch with table variables and user-defined functions that return datasets is that you should give them aliases when using them in queries that require joins.

    blindman
    Last edited by blindman; 06-27-03 at 11:00.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •