Nested Stored Procedure?

I have a stored procedure that returns a list of userIds that are available to the logged in user. I need to be able to use this list of userIds in another stored procedure whose purpose is to simply query a table for all results containing any of those userIds.

So if my first Stored Procedure returns this:
2
3
5
6

I need my select statement to do something like this:

select UserId, Column1, Column2
from Table1
where UserId = 2 or UserId = 3 or UserId = 5 or UserId = 6

I'm very new to stored procedures, can anyone help me with the syntax for doing this. I'm being pressured to get this done very quickly.

Thanks for any help!Huh? User ids like SQL Server User ids, or something application specific?

-PatP|||Eric1776,

There are 2 options to choose from:

Option 1 is to use a subselect, your select statement will look like this.

select UserId, Column1, Column2
from Table1
where UserID IN (Select UserID FROM UserID_Table)

Option 2 is to create a function instead of a stored procedure to return the valid User ID's, your script would look like this.

CREATE FUNCTION dbo.func_Return_UserID ()
RETURNS TABLE AS
RETURN SELECT UserID FROM UserID_Table
GO

select UserId, Column1, Column2
from Table1
where UserID IN (Select UserID FROM dbo.func_Return_UserID ())

Regards,
K3n|||Eric1776,

There are 2 options to choose from:

Option 1 is to use a subselect, your select statement will look like this.

select UserId, Column1, Column2
from Table1
where UserID IN (Select UserID FROM UserID_Table)

Option 2 is to create a function instead of a stored procedure to return the valid User ID's, your script would look like this.

CREATE FUNCTION dbo.func_Return_UserID ()
RETURNS TABLE AS
RETURN SELECT UserID FROM UserID_Table
GO

select UserId, Column1, Column2
from Table1
where UserID IN (Select UserID FROM dbo.func_Return_UserID ())

Regards,
K3n
Is it possible to use Option 1 with a stored procedure instead of a select statement?|||CREATE TABLE #tmp_user_list ( UserID INT)

INSERT INTO #tmp_user_list(UserID)
EXEC proc_name

SELECT * FROM #tmp_user_list -- Test only

select UserId, Column1, Column2
from Table1
where UserID IN (Select UserID FROM #tmp_user_list)

DROP TABLE #tmp_user_list

The above is how I solved this type of problem in SQL 7.0

Tim S|||CREATE TABLE #tmp_user_list ( UserID INT)

INSERT INTO #tmp_user_list(UserID)
EXEC proc_name

SELECT * FROM #tmp_user_list -- Test only

select UserId, Column1, Column2
from Table1
where UserID IN (Select UserID FROM #tmp_user_list)

DROP TABLE #tmp_user_list

The above is how I solved this type of problem in SQL 7.0

Tim S

Thanks! I should have thought of that. :) Its working great now.