xenium
asked on
How to execute a series of SQL statements that are generated from a query
Hi,
I'd like to execute a series of statements that are generated by a query, eg:
select 'grant select on ' + name + ' to public'
FROM (select name from sysobjects where (xtype='U' or xtype='V') and name not like '%load') as Tbls
order by name
(this generates statements like 'grant select on mytable to public')
I know i can use a command like exec (@sql) to execute sql that i've defined in a variable @sql, but how could i apply this to a whole list of statements like the above example? Maybe some sort of loop, or maybe i missing something quick & obvious (i'm quite new to this)
Thanks!
I'd like to execute a series of statements that are generated by a query, eg:
select 'grant select on ' + name + ' to public'
FROM (select name from sysobjects where (xtype='U' or xtype='V') and name not like '%load') as Tbls
order by name
(this generates statements like 'grant select on mytable to public')
I know i can use a command like exec (@sql) to execute sql that i've defined in a variable @sql, but how could i apply this to a whole list of statements like the above example? Maybe some sort of loop, or maybe i missing something quick & obvious (i'm quite new to this)
Thanks!
ASKER
> If it's something you need to do at runtime then create a cursor, loop through each row and EXEC.
Yes, this is what i want, how do i do it?
Yes, this is what i want, how do i do it?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
(yes, its a bad example i'll admit!)
Thanks, this works, and answers the question.
For extra points, i'd like to do this for many standard commands, is there a way to put this into an sp and give the SQL as a variable?
--> DECLARE exe_cur CURSOR FOR /* my query as a variable */
Thanks, this works, and answers the question.
For extra points, i'd like to do this for many standard commands, is there a way to put this into an sp and give the SQL as a variable?
--> DECLARE exe_cur CURSOR FOR /* my query as a variable */
ASKER
btw, since by answering this question, you;ve also answered my older q, you could post a pointer comment to get both points. https://www.experts-exchange.com/questions/22873280/how-to-grant-select-to-public-for-all-tables-and-views.html
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks a mill!
The bad bad example, in full: (WARNING: USE WITH EXTREME CARE!!)
exec sp_ExecuteQueries 'select ''grant select on '' + name + '' to public''
FROM (select name from sysobjects where (xtype=''U'' or xtype=''V'') and name not like ''%load'') as Tbls
order by name'
And the sp...
create procedure sp_ExecuteQueries @TemplateQuery varchar(8000) as
begin
DECLARE @sql VARCHAR(8000);
DECLARE @cur NVARCHAR(4000);
SELECT @cur = 'DECLARE exe_cur CURSOR FOR ' + @TemplateQuery ;
EXEC sp_executesql @cur;
OPEN exe_cur;
FETCH NEXT FROM exe_cur
INTO @sql;
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC (@sql);
FETCH NEXT FROM exe_cur
INTO @sql;
END;
CLOSE exe_cur;
DEALLOCATE exe_cur;
end
The bad bad example, in full: (WARNING: USE WITH EXTREME CARE!!)
exec sp_ExecuteQueries 'select ''grant select on '' + name + '' to public''
FROM (select name from sysobjects where (xtype=''U'' or xtype=''V'') and name not like ''%load'') as Tbls
order by name'
And the sp...
create procedure sp_ExecuteQueries @TemplateQuery varchar(8000) as
begin
DECLARE @sql VARCHAR(8000);
DECLARE @cur NVARCHAR(4000);
SELECT @cur = 'DECLARE exe_cur CURSOR FOR ' + @TemplateQuery ;
EXEC sp_executesql @cur;
OPEN exe_cur;
FETCH NEXT FROM exe_cur
INTO @sql;
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC (@sql);
FETCH NEXT FROM exe_cur
INTO @sql;
END;
CLOSE exe_cur;
DEALLOCATE exe_cur;
end
ASKER
Where the SQL statements are already generated, see also: https://www.experts-exchange.com/questions/22913366/How-to-execute-a-list-of-SQL-statements-that-are-generated-by-a-query.html
If it's a one-off exercise then I would just cut and paste the result into Management Studio and hit execute.
If it's something you need to do at runtime then create a cursor, loop through each row and EXEC.