Executing the results of a query

[dimbulbz]

I need to be able to execute the result set of a query. I have used xp_cmdshell, with an ISQL statement, but all I really want to do is call a new SQL widow from within SQL and execute, without all ISQL /U /P /Q"yadayada..." These are simple, one line queries that are loaded into a variable using a cursor:
*************************example*****************
declare @adduser as char (150)
declare crsr cursor for
select
'sp_adduser '''+rtrim(name)+''''+','''+rtrim(name)+''''+',''public'''
from tempuser
for read only
open crsr
while (0 = 0)
begin
fetch next from crsr into @adduser
if (@@fetch_status <> 0 ) break
exec master..xp_cmdshell @adduser
select @adduser
end
close crsr
deallocate crsr

********************** end example *****************

I want to replace the line

exec master..xp_cmdshell @adduser

with something that just opens a SQL session and executes the contents of @adduser.

Its probably really simple, but I'm stumped. Any help out there?



Jim Johnston
dimbulbz@hotmail.com

 

[NathanGriffiths]

EXECUTE can be used to execute a T-SQL string held in a variable, so you could rewrite the proc something like this;

declare @adduser as [red]VARCHAR(150)[/red]
declare crsr  cursor [red]LOCAL FAST_FORWARD[/red] for 
select  
   'sp_adduser '''+rtrim(name)+''''+','''+rtrim(name)+''''+',''public'''
    from tempuser

open crsr 
[red]fetch next from crsr into @adduser[/red]
while [red]@@Fetch_Status = 0[/red]
 begin 
   [red]exec (@adduser)[/red]
   select @adduser
   fetch next from crsr into @adduser
 end
close crsr
deallocate crsr

Nathan