Set @variable with a value surrounded by single quotes

Question

Post reply

Set @variable with a value surrounded by single quotes

Paul McCurdy

Ten Centuries

Points: 1377
More actions
May 8, 2008 at 9:37 am

#186302

I'm trying to build a dynamic SQL routine that does a SELECT from a table, and also includes as 1st column in results a name that I assign dynamically. But I can't get it to use 'vho_cd' as 1st column.
DECLARE @Query varchar(8000), @ColumnName varchar(50), @vho char(6)
SELECT @ColumnName = ChannelMapName
FROM myTable WHERE idNum = 1
SELECT @vho = vho_cd
FROM myTable WHERE idNum = 1
-- in the following, I want vho_cd listed as 1st column. It says vho_cd is an invalid column name.
SET @Query = '
SELECT
@vho,
COALESCE(ViewerChannel,0),
CASE WHEN [' + @ColumnName + '] >= 0 THEN CAST([' + @ColumnName + '] AS DateTime) ELSE CAST(0 AS DateTime)END "EffDate",
CASE WHEN [' + @ColumnName + '] < 0 THEN CAST(ABS([' + @ColumnName + ']) AS DateTime) ELSE CAST(50768 AS DateTime)END "EndDate"
FROM myTable2
WHERE [' + @ColumnName + '] <> 50770
'
PRINT @Query
PRINT ''
HELP!!

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

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Use this as a starting pointing:

DECLARE @Query varchar(8000), @ColumnName varchar(50), @vho char(6)

--SELECT @ColumnName = ChannelMapName

--FROM myTable WHERE idNum = 1

--SELECT @vho = vho_cd

--FROM myTable WHERE idNum = 1

set @ColumnName = 'AColumn'

set @vho = 'BColumn'

-- in the following, I want vho_cd listed as 1st column. It says vho_cd is an invalid column name.

SET @Query = 'SELECT' + char(13) + char(10) + ' ' + @vho + ',' + char(13) + char(10) +

' COALESCE(ViewerChannel,0),' + char(13) + char(10) +

' CASE WHEN [' + @ColumnName + '] >= 0 THEN CAST([' + @ColumnName + '] AS DateTime) ELSE CAST(0 AS DateTime)END as EffDate,' + char(13) + char(10) +

' CASE WHEN [' + @ColumnName + '] < 0 THEN CAST(ABS([' + @ColumnName + ']) AS DateTime) ELSE CAST(50768 AS DateTime) END as EndDate' + char(13) + char(10) +

'FROM myTable2' + char(13) + char(10) +

'WHERE [''' + @ColumnName + '''] <> 50770' + char(13) + char(10)

PRINT @Query

PRINT ''

😎

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 2

You need to move the variable outside the quotes

select @cmd = 'select ' + @var + '...'

Paul McCurdy Ten Centuries Points: 1377 More actions · Answer 3

Thanks for the suggestions, but it still returns results without single quotes, so thinks it's looking for a column name. i.e. instead of SELECT vho_cd ..... I want SELECT 'vho_cd' , ......

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Play with the query a bit, you'll find it simple to get the ' into it. Something like this ''' instead of '.

😎

Paul McCurdy Ten Centuries Points: 1377 More actions · Answer 5

Thanks - you're right. I just needed to add enough ticks +''''+@vho+''''+.......

🙂