Problem in case statement - SQL Server Forums

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)

Problem in case statement

Author

Topic

Sql_forum
Yak Posting Veteran

50 Posts

Posted - 2011-11-08 : 09:37:05

HI
I have columns INSTPR,INSPRM01 to INSPRM99 in my table
i want to fetch value from these columns based upon input @Age

Declare @Age as int
SELECT CASE @Age
WHEN '0'
THEN INSTPR
ELSE
'INSPRM'+convert(varchar(2),@Age) --( want to fetch value from INSPRM01 column if age=01)
END as RATE from TBL_Name

but when i execute the above qry i m getting the error as below:
I gave @Age as 01
Error in converting varchar to numeric.

Need help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-08 : 09:39:25

you need dynamic sql for this

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Sql_forum
Yak Posting Veteran

50 Posts

Posted - 2011-11-08 : 09:43:31

As per my observation

'INSPRM'+convert(varchar(2),@Age) considered as value in else statement rather than as column name.
can any ony suggest how to convert into column name???

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-08 : 10:13:13

quote:
Originally posted by Sql_forum

As per my observation

'INSPRM'+convert(varchar(2),@Age) considered as value in else statement rather than as column name.
can any ony suggest how to convert into column name???

just remove '' and use it in dynamic sql

other way of doing it is using UNPIVOT

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Sql_forum
Yak Posting Veteran

50 Posts

Posted - 2011-11-08 : 10:39:33

Can you please elaborate on how to use in dynamic sql???

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-08 : 10:51:52

[code]
DECLARE @SQL nvarchar(4000)
declare @params nvarchar(1000)
Declare @Age as int
SET @Age=1
SET @Params = N' @Age as int'
SET @SQL='SELECT CASE @Age
WHEN ''0''
THEN INSTPR
ELSE
INSPRM'+right( '00' + convert(varchar(2),@Age),2) +
' END as [RATE] from TBL_Name'
PRINT(@SQL)
EXEC sp_executesql @SQL, @params,@Age=@Age
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources