当前位置: 动力学知识库 > 问答 > 编程问答 >

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

问题描述:

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?

网友答案:
SELECT * FROM dbo.aspnet_UsersInRoles 
WHERE UserId IN ( 
    SELECT UserId FROM aspnet_Users 
    WHERE UserName = @username  
) 
网友答案:

this should do it ..

SELECT 
  * 
FROM 
  dbo.aspnet_UsersInRoles 
WHERE 
  UserId IN ( 
             SELECT 
               UserId 
             FROM 
               aspnet_Users 
             WHERE 
               UserName = @username
       )
网友答案:
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 *" */
网友答案:

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 [email protected]

/*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
分享给朋友:
您可能感兴趣的文章:
随机阅读: