SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Exec query which is returned by another query		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

DTs
Starting Member

2 Posts
Posted - 01/19/2013 :  10:15:55  Show Profile  Reply with Quote
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!
Edited by - DTs on 01/19/2013 11:27:13

James K
Flowing Fount of Yak Knowledge

1527 Posts
Posted - 01/19/2013 :  11:59:24  Show Profile  Reply with Quote
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.
Go to Top of Page

DTs
Starting Member

2 Posts
Posted - 01/19/2013 :  12:11:51  Show Profile  Reply with Quote
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!
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

1527 Posts
Posted - 01/19/2013 :  15:03:26  Show Profile  Reply with Quote
You are very welcome - glad to be of help.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000