Minor Dynamic SQL Assistance Needed

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

SQL Server 2008 Forums

Transact-SQL (2008)

Minor Dynamic SQL Assistance Needed

Author

Topic

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2012-01-09 : 08:18:34

Can someone help me out with the following SQL statement?
I am trying to get the where clause to work, and can't get my quotes right....

declare @dbName char(20)
set @dbName = 'DEV_DB'
declare @sql varchar(4000)
select @sql = 'select * from ' + @dbName + '.dbo.TableName' + 'where ARColumn = ' '' '
exec (@sql)

Thanks!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-01-09 : 08:22:16

[code]
select @sql = 'select * from ' + @dbName + '.dbo.TableName ' + 'where ARColumn = ' '''' '
exec (@sql)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-01-09 : 08:24:29

declare @dbName char(20)
set @dbName = 'DEV_DB'
declare @sql varchar(4000)
select @sql = 'select * from ' + @dbName + '.dbo.TableName' + 'where ARColumn = '''''
exec (@sql)

For more information refer this
http://beyondrelational.com/blogs/madhivanan/archive/2008/02/19/understanding-single-quotes.aspx

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-01-09 : 08:29:48

Again

Madhivanan

Failing to plan is Planning to fail

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2012-01-09 : 10:42:34

Thank you, I appreciate the help and additional info.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-01-09 : 10:46:15

2 things:

1) @dbName is CHAR(20). You probably want a VARCHAR?
2) SQL Injection Attack. Unless you treat @dbName with suspicion then you are leaving yourself open to abuse.

QUOTENAME is pretty good.


DECLARE @foo sysname

SET @foo = 'sys.tables; DROP TABLE Sales; --'

PRINT QUOTENAME(@foo)

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources