How to execute SQL in query reults?

Question

Post reply

How to execute SQL in query reults?

romanoplescia

SSCommitted

Points: 1787
More actions
May 10, 2010 at 7:42 am

#224397

Hi guys and gals, I have written a piece of SQL that produces SQL as its results. The next step I want to do is to execute the sql that the query produces and automate the whole thing. I was hoping there would be a way to do it in SSIS but I'm a bit stuck at the moment, any ideas on how I can do this?
Here is the SQL:
USE Database_name
GO
set nocount on
select
'use [Database_name] ' + char(10) +
'GRANT VIEW DEFINITION ON [dbo].[' + name + '] TO [DEVELOPER]' + char(10)
from sysobjects where xtype = 'P'

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

irobertson Hall of Fame Points: 3695 More actions · Answer 1

Hi,

You need to pass the results of your query to a variable, then call this using either exec or preferably sp_executesql.

I'm assuming that you'll have more than one stored procedure in sysobjects. In this case you'll have to loop through the rows returned by the query.

Before you do any of this, reading this article would be a good idea:

http://www.sommarskog.se/dynamic_sql.html

From the looks of it, you want to batch assign permissions. This:

http://msdn.microsoft.com/en-us/library/aa176682(SQL.80).aspx looks a lot safer to me...

Cheers, Iain

romanoplescia SSCommitted Points: 1787 More actions · Answer 2

romanoplescia

SSCommitted

Points: 1787

May 10, 2010 at 9:49 am

#1165086

Thanks for the advice 😀

Jeff Moden SSC Guru Points: 1004425 More actions · Answer 3

irobertson (5/10/2010)
Hi,
You need to pass the results of your query to a variable, then call this using either exec or preferably sp_executesql.
I'm assuming that you'll have more than one stored procedure in sysobjects. In this case you'll have to loop through the rows returned by the query.
Before you do any of this, reading this article would be a good idea:
http://www.sommarskog.se/dynamic_sql.html
From the looks of it, you want to batch assign permissions. This:
http://msdn.microsoft.com/en-us/library/aa176682(SQL.80).aspx looks a lot safer to me...
Cheers, Iain

No need for any kind of explicit loop here...

USE Database_name

GO

DECLARE @sql NVARCHAR(MAX)

SELECT @sql = (SELECT 'GRANT VIEW DEFINITION ON [dbo].'+QUOTENAME(name)+' TO [DEVELOPER]' + CHAR(10)

FROM sysObjects WHERE xtype = 'P' FOR XML PATH('') )

PRINT @sql

-- EXEC (@SQL) -- Uncomment this when done with testing

IIRC, dbo.sysObjects has been deprecated as a legacy view and shouldn't be used for new code.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeffrey Williams SSC Guru Points: 90309 More actions · Answer 4

No need to even loop through all objects in the database. You can grant view definition to all objects in a schema in a single command:

GRANT VIEW DEFINITION ON SCHEMA::dbo TO [DEVELOPER];

The advantage of this approach is that adding new objects does not require adding new permissions. The permission is on the schema, so new objects are already included.

This will not restrict the view definition to just procedures though - so make sure you want to grant that right to all objects in the schema.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs