Very Computer

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

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.