Reusing Query Result Rowsets within a Stored Procedure

Reusing Query Result Rowsets within a Stored Procedure

Post by Scott Hutchinso » Fri, 24 May 2002 08:44:29



How should I write the stored procedure shown below? The final WHERE clause
throws an error. My goal is to reuse the rowset returned by the
uspFavoriteSelect stored procedure (or I'd be willing to use a function or
whatever) in the final WHERE clause, so I don't have to write or run the
same query twice. I've tried to use a user-defined function that returns a
table variable instead of the uspFavoriteSelect stored procedure, but it
griped about inserting identity values, and I couldn't get it to run. It
also wouldn't let me sort the table with an ORDER BY clause, but I could
probably live with that limitation. I'm about ready to try a temporary
table, but it seems like this could be done more efficiently with a table
variable.

CREATE Procedure dbo.uspThreeTablesSelect
(

)
AS


SELECT
    CategoryID,
    CategoryName

FROM
    dbo.Categories

ORDER BY
    CategoryName;

SELECT
    FavoriteID,
    CategoryID,
    Priority

FROM
    dbo.CategoryMembers

WHERE

GO

Scott Hutchinson


 
 
 

Reusing Query Result Rowsets within a Stored Procedure

Post by linda deng[M » Fri, 24 May 2002 14:30:44


Hi Scott,

        The following codes have been tested and can be executed successfully. You
can create an UDF using table variable, and you can call this function in
the SP like this:

        CREATE Procedure dbo.uspThreeTablesSelect
        (

        )
        AS

        SELECT CategoryID,CategoryName FROM dbo.Categories ORDER BY CategoryName;

        SELECT employeeID,lastname, firstname FROM dbo.Employees

        CREATE Function dbo.uspFavoriteSelect
        (

        )

        ( favoriteID int primary key not null )
        AS
                BEGIN

                RETURN
                END

Sincerely,

Linda Deng
Microsoft Support Engineer

This posting is provided "AS IS" with no warranties, and confers no rights.

 
 
 

1. Retrieving result from a stored procedure called within another stored procedure

Hello,

I have a stored procedure I created that is calling another stored procedure
within
another application which returns a result set.  So keep in mind I can't
modify the
stored procedure I am calling.

I am trying to access the result set produced by the called stored procedure
with
my stored procedure.

Is there any other way to retrieve these results other than dumping them
into
a temp table?

Thanks

2. Fantasia

3. Using query results within a stored procedure

4. Stored Procedure Help

5. How do I retrieve the results of a dynamic query within a store procedure

6. Sybase on IBM RS6000/SP2

7. Using Query Results within Stored Procedure

8. DBA & User Name

9. Newbie Script ?: Using VBScript Classes or Calling Stored Procedures (How to Reuse Code within VBScript)

10. Returning a query result from a stored procedure into another stored procedure

11. Discarding Results from Within a Stored Procedure

12. Calling a Stored Procedure from within a Stored Procedure

13. Calling a stored procedure within a stored procedure.