SQL Server Performance Forum – Threads Archive

CASE WHEN statement…

when i execute the below case statement, i get a date value even if i pass int as the case parameter. this is because of the getdate() function in the case statement, the integer value is converted into datetime datatype. if we comment that particular line then the integer value is converted into a float value and displayed. can anybody tell me why CASE statement behaves like this and an alternate to do this.. DECLARE @query varchar(255)
,@datatype varchar(20)
select @datatype = ‘int’ — i am getting this @datatype value dynamically from a table.
SELECT @query = CASE @datatype WHEN ‘float’ THEN convert(numeric(6,2),(rand(12442) * 100))
WHEN ‘int’ THEN convert(int,right(convert(varchar(30),rand(1)),4))
WHEN ‘datetime’ THEN getdate()
ELSE NULL END
select @query
Thanks,
Ram "It is easy to write code for a spec and walk in water, provided, both are freezed…"

You’re depending on implicit conversion to varchar, so what do you expect? Solution is to add CAST(… AS VARCHAR(255)) within each THEN clause. SELECT @query = CASE @datatype
WHEN ‘float’ THEN CAST(convert(numeric(6,2),(rand(12442) * 100)) AS VARCHAR(255))
WHEN ‘int’ THEN CAST(convert(int,right(convert(varchar(30),rand(1)),4)) AS VARCHAR(255))
WHEN ‘datetime’ THEN CAST(getdate() AS VARCHAR(255))
ELSE NULL END Alternatively – but impractical when working set-based: IF @datatype = ‘float’
SET @query = convert(numeric(6,2),(rand(12442) * 100))
ELSE
IF @datatype = ‘int’
SET @query = convert(int,right(convert(varchar(30),rand(1)),4))
ELSE
IF @datatype = ‘datetime’
SET @query = getdate()

Try this
WHEN ‘datetime’ THEN CAST(getdate() AS VARCHAR(20)) And read about it in BOL under data type precedences —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)

Adriaan, <pre id="code"> THEN CAST(getdate() AS VARCHAR(255)) </pre id="code"> that Access mentality can be cured, I am told.[<img src=’/community/emoticons/emotion-1.gif’ alt=’

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by FrankKalis Adriaan, <pre id="code"> THEN CAST(getdate() AS VARCHAR(255)) </pre id="code"> that Access mentality can be cured, I am told.[<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a> Ich unterstuetze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <hr height="1" noshade id="quote"></blockquote id="quote">To be honest, Frank, I do this more often in T-SQL than I ever did in Access.[<img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ />]

Check this
DECLARE @query varchar(255)
,@datatype varchar(200)
select @datatype = ‘int’ — i am getting this @datatype value dynamically from a table. SELECT
CASE WHEN @datatype =’float’ THEN cast(rand(12442) * 100 as varchar(150))
WHEN @datatype =’int’ THEN right(convert(varchar(30),rand(1)),4)
WHEN @datatype =’datetime’ THEN convert(varchar,getdate() ,103)
END —————————————-
http://spaces.msn.com/members/dineshasanka

Thanks ppl.[<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] Thanks, Ram "It is easy to write code for a spec and walk in water, provided, both are freezed…"

]]>