using variable in select statement

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 2005 Forums

Transact-SQL (2005)

using variable in select statement

Author

Topic

rum23
Yak Posting Veteran

77 Posts

Posted - 2009-06-25 : 02:25:47

Can somebody tell me what is wrong in the SQL below?

declare @qurterlyCost as nvarchar(100)
@quarterlyCost='Q1Cost'
SELECT @quarterlyCost FROM tableA

Thanks much!

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-06-25 : 02:36:57

declare @quarterlyCost as nvarchar(100)

DECLARE @sql as varchar(1000)
SET @quarterlyCost='*'
SET @sql = 'SELECT '+ @quarterlyCost+ 'FROM tableA'
EXEC (@sql)
something liddat?

rum23
Yak Posting Veteran

77 Posts

Posted - 2009-06-25 : 02:47:44

PERFECTOOOO!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-25 : 02:53:39

And just to prepare for unpleasent surprises in the future...

DECLARE @sql as varchar(1000)
SET @quarterlyCost = '*'

If @quarterlyCost <> '*'
set @quarterlyCost = quotename(@quarterlyCost)

SET @sql = 'SELECT ' + @quarterlyCost + ' FROM tableA'
EXEC (@sql)

E 12°55'05.63"
N 56°04'39.26"

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-06-25 : 02:57:00

quote:
Originally posted by Peso

And just to prepare for unpleasent surprises in the future...

DECLARE @sql as varchar(1000)
SET @quarterlyCost = '*'

If @quarterlyCost <> '*'
set @quarterlyCost = quotename(@quarterlyCost)

SET @sql = 'SELECT ' + @quarterlyCost + ' FROM tableA'
EXEC (@sql)

E 12°55'05.63"
N 56°04'39.26"

hi, peso
quotename is to prevent injection??

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-25 : 03:04:02

That too.

Try your suggestion and set @quarterlyCost to 'My column name with spaces'
or set @quarterlyCost to '1ColumnWithoutSpacesButStartsWithNumber'

E 12°55'05.63"
N 56°04'39.26"

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-06-25 : 03:08:45

oh! i see that now... never know got this quotename 'thingy'
ps. Please follow peso answer

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-06-25 : 05:40:54

Make sure you read this
www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2009-06-25 : 05:48:48

check sp_executesql

[url]http://msdn.microsoft.com/en-us/library/ms188001.aspx[/url]

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-25 : 06:38:35

quote:
Originally posted by pelegk2

check sp_executesql

Which only handles parameter values, not tablenames and columnnames.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources