Can I pass multiple rows from a stored procedure query into another query?

Question

In my previous question I asked about storing the result of a query in a variable... now I realize the query can return multiple rows.

I currently have this:

SELECT @UserId = UserId FROM aspnet_Users WHERE UserName = @username

And I want to do something like this:

DELETE FROM some_table WHERE UserId IN ( *the ID list* )
DELETE FROM some_table_2 WHERE UserId IN ( *the ID list* )

My first instinct is to use "GROUP_CONCAT" but apparently that's a MySQL-only feature. There are some ways to make equivalent functionality, but I'm wondering if there is a better way to structure the queries?

Answer 1

SELECT * FROM dbo.aspnet_UsersInRoles 
WHERE UserId IN ( 
    SELECT UserId FROM aspnet_Users 
    WHERE UserName = @username  
) 

Answer 2

this should do it ..

SELECT 
  * 
FROM 
  dbo.aspnet_UsersInRoles 
WHERE 
  UserId IN ( 
             SELECT 
               UserId 
             FROM 
               aspnet_Users 
             WHERE 
               UserName = @username
       )

Answer 3

delete from st
    from some_table st
        inner join aspnet_Users au
            on st.UserId = au.UserId
    where /* add additional criteria here to produce "* the ID list *" */

Answer 4

If you want to avoid to repeat subquery you can put its result into a temp table or a table variable. For instance:

/*declare and fill a table variable containing all user ids you need to manipulate*/
declare @t table(userid int)
insert into @t(userid) select UserId from aspnet_Users where UserName=@username

/*delete from some table by using the temp variable*/
delete from st 
from some_table st 
    inner join @t t 
        on st.userid = t.userid 
/*repeat this for some other table*/
delete from st 
from some_other_table st 
    inner join @t t 
        on st.userid = t.userid 

If you want to avoid multiple delete statements and if presence of user ids in some_other_table doesn't make sense if this doesn't exist in the some_table then you can create trigger on some_table:

create trigger x on some_table for delete
    as
    begin
     delete from some_other_table 
     where userid in (select userid from deleted)
  end