Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2004
    Posts
    8

    Unanswered: Using a stored procedure in a inner join of a stored procedure

    Hi,

    it is possible to execute a stored procedures inside of a inner join of another stored procedure?
    I have the problem, that I have a stored procedure which will return a resultset. This resultset should be used in a join of another stored procedure. It is possible or not?
    I tried something like that, but it doesn't work for me:
    Code:
    CREATE PROCEDURE usp_Person
    AS
    
    SELECT
      A.LastName,
      A.FirstName,
      B.Street
    FROM
      Table1 A
        INNER JOIN
      EXEC usp_Address B
        ON
          A.Id = B.Id
    
    GO
    I am thankful for a hint or a better way.

  2. #2
    Join Date
    Aug 2004
    Posts
    10
    there are two ways to accomplish that.
    you can use user defined functions that return a table
    or you can rewrite your stored procedure as a view if possible.

    check out 'User-Defined Functions That Return a table Data Type' in BOL

    hth,
    Cristian Babu

  3. #3
    Join Date
    Aug 2004
    Posts
    10
    you can olso use OpenQuery to join with the result of a stored procedure, but this may be too much overhead to develop

    hth,
    Cristian Babu

  4. #4
    Join Date
    Aug 2004
    Posts
    10
    you can olso use OpenQuery to join with the result of a stored procedure, but this may be too much overhead to develop

    hth,
    Cristian Babu

  5. #5
    Join Date
    Oct 2004
    Posts
    8
    thanks for reply, but now i have a problem with the function.
    the following doesn't work:
    Code:
    SET QUOTED_IDENTIFIER ON 
    GO
    SET ANSI_NULLS ON 
    GO
    
    CREATE FUNCTION biAus_GetAddress()
    RETURNS @ResultTable TABLE
    (
      id int,
      street nvarchar(255),
    )
    AS
    BEGIN
      INSERT @ResultTable
        EXEC usp_Address
      RETURN
    END
    
    
    GO
    SET QUOTED_IDENTIFIER OFF 
    GO
    SET ANSI_NULLS ON 
    GO
    Now I get an error which means that I can not use EXECUTE for inserting into the table. What gets wrong?

    ps: it is possible to use an existing table declaration to declare the ResultTable or do I have to do it in the way above and declare all columns again?
    Last edited by torrud; 10-28-04 at 11:13.

  6. #6
    Join Date
    Aug 2004
    Posts
    10
    well, you cannot use insert statement in a user defined function unless it's against a table datatype variable. you're not allowed to insert anything into a table or a temporary table.

    hth,
    Cristian Babu

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I think that the intent was to convert usp_Address into a table valued function, not to create a "wrapper function" that you'd populate by calling usp_Address.

    -PatP

Posting Permissions

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