Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2003
    Posts
    12

    Unanswered: 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!

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Huh? User ids like SQL Server User ids, or something application specific?

    -PatP

  3. #3
    Join Date
    Mar 2005
    Location
    Philippines, Makati
    Posts
    11
    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

  4. #4
    Join Date
    Oct 2003
    Posts
    12
    Quote Originally Posted by 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?

  5. #5
    Join Date
    Mar 2003
    Location
    Indiana, USA
    Posts
    100
    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
    Last edited by TimS; 03-09-05 at 10:45.

  6. #6
    Join Date
    Oct 2003
    Posts
    12
    Quote Originally Posted by TimS
    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.

Posting Permissions

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