I need to execute the procedure deleteQuestion
for each element that was returned by this select query:
select id from questions where Stuff = @Stuff
execute deleteQuestion id
something like:
exe开发者_如何学运维cute deleteQuestion each(select id fom questions where Stuff = @Stuff)
anybody knows how ?
Use a cursor:
DECLARE @Id INT
DECLARE your_cursor CURSOR FOR
SELECT id from questions where Stuff = @Stuff
OPEN your_cursor
FETCH NEXT FROM your_cursor
INTO @Id
WHILE @@FETCH_STATUS = 0
BEGIN
execute deleteQuestion @Id
FETCH NEXT FROM your_cursor
INTO @Id
END
CLOSE your_cursor
DEALLOCATE your_cursor
I did it like this:
declare @sqlstr nvarchar(max)
set @sqlstr = ''
select @sqlstr = @sqlstr + ' exec deleteQuestion ' + cast(q.id as nvarchar(max))
from Questions q where stuff = @stuff
exec (@sqlstr)
I've been told that this approach is much faster than a cursor
精彩评论