Dynamic SQL

Question

Dynamic SQL

_ms65g_

SSCommitted

Points: 1504
More actions
May 19, 2010 at 12:23 pm

#221780

Hi,
This statement is correct:
SELECT 'String'
But when I try to use dynamic it not be correct, How can I do that?
EXECUTE('SELECT 'String'')
Thanks!

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

_ms65g_ (5/19/2010)
Hi,
This statement is correct:
SELECT 'String'
But when I try to use dynamic it not be correct, How can I do that?
EXECUTE('SELECT 'String'')
Thanks!

every quote that exists between your forst and last quotes need to be escaped out with an additional quote:

EXECUTE('SELECT ''String'' ')

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

_ms65g_ SSCommitted Points: 1504 More actions · Answer 2

_ms65g_

SSCommitted

Points: 1504

May 19, 2010 at 12:27 pm

#1169664

Thank you very much!

_ms65g_ SSCommitted Points: 1504 More actions · Answer 3

I use database name before the INFORMATION_SCHEM view but when I use the USE statement with another database name my result is incorrect.

I have two database with names "concat" and "Northwind. And I use following FROM clause at the two queries:

FROM [concat].INFORMATION_SCHEMA.COLUMNS

See:

USE concat

SELECT 'UPDATE ' + 'concat' + '..[' + table_name + ']' +

' SET ' + '[' + column_name + ']' + ' = ' + column_default +

' WHERE ' + '[' + column_name + '] IS NULL' + ';'

FROM [concat].INFORMATION_SCHEMA.COLUMNS

WHERE column_default IS NOT NULL

AND OBJECTPROPERTY(OBJECT_ID(table_name),'istable') = 1;

/*

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

UPDATE concat..[newOrders] SET [order_date] = (getdate()) WHERE [order_date] IS NULL;

UPDATE concat..[newOrders] SET [amount] = ((0)) WHERE [amount] IS NULL;

UPDATE concat..[Orders] SET [order_date] = (getdate()) WHERE [order_date] IS NULL;

UPDATE concat..[Orders] SET [amount] = ((0)) WHERE [amount] IS NULL;

UPDATE concat..[ttttttt] SET [j] = ((200)) WHERE [j] IS NULL;

*/

use Northwind

SELECT 'UPDATE ' + 'concat' + '..[' + table_name + ']' +

' SET ' + '[' + column_name + ']' + ' = ' + column_default +

' WHERE ' + '[' + column_name + '] IS NULL' + ';'

FROM [concat].INFORMATION_SCHEMA.COLUMNS

WHERE column_default IS NOT NULL

AND OBJECTPROPERTY(OBJECT_ID(table_name),'istable') = 1;

/*

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

UPDATE concat..[Orders] SET [order_date] = (getdate()) WHERE [order_date] IS NULL;

UPDATE concat..[Orders] SET [amount] = ((0)) WHERE [amount] IS NULL;

*/

now What do I do?

Steve Cullen SSCertifiable Points: 5598 More actions · Answer 4

From OBJECTPROPERTY BOL:

The Database Engine assumes that object_id is in the current database context. A query that references an object_id in another database will return NULL or incorrect results...

Converting oxygen into carbon dioxide, since 1955.

_ms65g_ SSCommitted Points: 1504 More actions · Answer 5

Steve Cullen (5/19/2010)
From OBJECTPROPERTY BOL:
The Database Engine assumes that object_id is in the current database context. A query that references an object_id in another database will return NULL or incorrect results...

Good tip, I did not notice the OBJECT_ID function!

Thanks