Exec query which is returned by another query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Exec query which is returned by another query

Author

Topic

DTs
Starting Member

2 Posts

Posted - 2013-01-19 : 10:15:55

Hi. I have this query


SELECT 'ALTER TABLE DOC_INVS_1 DROP CONSTRAINT ' + CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'DOC_INVS_1'
and CONSTRAINT_NAME LIKE 'FK__DOC_INVS___kntr%'

Which returns a query like this:

ALTER TABLE DOC_INVS_1 DROP CONSTRAINT FK__DOC_INVS___kntr___2018A105

How to execute the returned query? I need to do it with another query (copy and paste to new query window is not an option)

I thought something like


EXEC (<the above query>)

But I guess there's some problems with the quotation marks or something else ? I've seen somewhere the prefix

N`

which I guess is also used for escaping the quotation marks.

Thanks for the replies!

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-19 : 11:59:24

Wouldn't the query return multiple rows? You cannot pass a record set to EXEC. If the objective is to execute all the strings returned by the query, the following might be an option. I have not tested this on any of my tables; the goal is to concatenate all the rows with a semi-colon separating them, which can then be executed. If you expect only one row to be returned, then you don't need the FOR XML PATH('') clause

DECLARE @sql NVARCHAR(MAX);
SELECT @sql = c
FROM
(
	SELECT 'ALTER TABLE DOC_INVS_1 DROP CONSTRAINT ' + CONSTRAINT_NAME + '; '
	FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'DOC_INVS_1'
	and CONSTRAINT_NAME LIKE 'FK__DOC_INVS___kntr%' FOR XML PATH('')
) T(c);
EXEC(@sql);

All the usual warnings and concerns about dynamic SQL apply.

DTs
Starting Member

2 Posts

Posted - 2013-01-19 : 12:11:51

Yes, I expect only one row to be returned because there's no more than one constraint like the above in each table.

Anyway, your code works like charm! Thanks a lot!

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-19 : 15:03:26

You are very welcome - glad to be of help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources