why does this Dynamic Sql Statement fail? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

why does this Dynamic Sql Statement fail?

Author

Topic

boathead
Starting Member

5 Posts

Posted - 2013-06-03 : 20:29:45

This is no any problem:

select COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,IS_NULLABLE
from [AdventureWorks2012].INFORMATION_SCHEMA.COLUMNS WHERE table_name='employee';

But, when it moved to a dynamic statement, it always fail:

begin
DECLARE @sqlstatement VARCHAR(MAX);
DECLARE @TableName varchar(max)='employee';

set @sqlstatement='select COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,IS_NULLABLE
from [AdventureWorks2012].INFORMATION_SCHEMA.COLUMNS WHERE table_name='+@TableName;
print (@sqlstatement);
EXECUTE(@sqlstatement);

end

error says:Invalid column name 'employee'

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2013-06-03 : 21:41:44

Change it to this:
set @sqlstatement='select COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,IS_NULLABLE
from [AdventureWorks2012].INFORMATION_SCHEMA.COLUMNS WHERE table_name='''+@TableName+'''';

If you looked at the output of your print statement it would be obvious

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-06-03 : 21:54:07

You just need some extra quotes around tablename as shown in red below:

quote:
Originally posted by boathead

This is no any problem:
[CODE]
select COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,IS_NULLABLE
from [AdventureWorks2012].INFORMATION_SCHEMA.COLUMNS WHERE table_name='employee';

But, when it moved to a dynamic statement, it always fail:

begin
DECLARE @sqlstatement VARCHAR(MAX);
DECLARE @TableName varchar(max)='''employee''';

set @sqlstatement='select COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,IS_NULLABLE
from [AdventureWorks2012].INFORMATION_SCHEMA.COLUMNS WHERE table_name='+@TableName;
print (@sqlstatement);
EXECUTE(@sqlstatement);

end
[/CODE]

error says:Invalid column name 'employee'

boathead
Starting Member

5 Posts

Posted - 2013-06-03 : 23:09:40

Thanks,guys.I totally mixed @sqlstatement string with a string in statement.:(

NeilG
Aged Yak Warrior

530 Posts

Posted - 2013-06-04 : 04:59:00

quote:
Originally posted by boathead

This is no any problem:

select COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,IS_NULLABLE
from [AdventureWorks2012].INFORMATION_SCHEMA.COLUMNS WHERE table_name='employee';

But, when it moved to a dynamic statement, it always fail:

begin
DECLARE @sqlstatement VARCHAR(MAX);
DECLARE @TableName varchar(max)='employee';

set @sqlstatement='select COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,IS_NULLABLE
from [AdventureWorks2012].INFORMATION_SCHEMA.COLUMNS WHERE table_name='+@TableName;
print (@sqlstatement);
EXECUTE(@sqlstatement);

end

error says:Invalid column name 'employee'

Try not to use EXECUTE(@sqlstatement)

use sp_exexutesql statement instead

-----------------------------------------------
Learning something new on SQL Server everyday.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2013-06-04 : 10:58:38

1) there is no need for dynamic sql in your statement?


SELECT COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,IS_NULLABLE
from [AdventureWorks2012].INFORMATION_SCHEMA.COLUMNS WHERE table_name= @TableName

Will work -- there is no dynamic need.

What are you actually trying to do?

In general:
2) Always use sp_executeSql -- only use string concatenation for dynamic table or column names. -- everything else should be parametrisable.

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources