April 14, 2008 at 5:49 pm
Hi There,
I trying to use the TSQL case statement to return a varchar value relating to a column. I'm passing in the value of int and it's telling me that it can't convert from int to varchar. I tried to cast the value of @Section to varchar so I could return the value but no luck.
Can someone give me a hand to fix this please?
Sean
declare @HasAnyRows int
DECLARE @COL VARCHAR(50)
DECLARE @Section int
SELECT @Section = 1
SELECT @HasAnyRows = 0
IF @HasAnyRows <= 0
BEGIN
SELECT @COL = CASE @Section
WHEN @Section = 1 THEN @COL = 'columnname'
ELSE 0
END
END
April 14, 2008 at 6:13 pm
declare @HasAnyRows int
DECLARE @COL VARCHAR(50)
DECLARE @Section int
SELECT @Section = 1
SELECT @HasAnyRows = 0
IF @HasAnyRows <= 0
BEGIN
SELECT @COL = CASE @Section
WHEN @Section = 1 THEN @COL = 'columnname'
ELSE 0
END
END
Looks like you have a few problems here:
first is with your THEN condition. You're already setting the @Col variable, so get rid of the reasignment.
SELECT @COL = CASE @Section
WHEN @Section = 1 THEN 'columnname'
ELSE 0
END
END
Second problem: you must think of the case statement as a function that returns a value. In your case, in one logic branch, you're returning a character type, and in the other you're returning an int, which is why it is complaining. So:
SELECT @COL = CASE @Section
WHEN @Section = 1 THEN 'columnname'
ELSE '0'
END
END
That should do it.
If you're dealing with an integer type column in a select, then do something like:
SELECT @COL = CASE @Section
WHEN @Section = 1 THEN 'columnname'
ELSE cast(intcolumn as varchar(x))
END
END
Hope this helps.
April 15, 2008 at 2:58 am
declare @HasAnyRows int
DECLARE @COL VARCHAR(50)
DECLARE @Section int
SELECT @Section = 1
SELECT @HasAnyRows = 0
IF @HasAnyRows <= 0 and @section = 1
SELECT @COL = 'columnname'
else
SELECT @COL = 0
April 15, 2008 at 3:26 am
[font="Verdana"]
Samuel Vella (4/15/2008)
declare @HasAnyRows int
DECLARE @COL VARCHAR(50)
DECLARE @Section int
SELECT @Section = 1
SELECT @HasAnyRows = 0
IF @HasAnyRows <= 0 and @section = 1
SELECT @COL = 'columnname'
else
SELECT @COL = 0
SELECT @Section = 1, @HasAnyRows = 0
No need to write separate select for assigning values for multiple variables. Within the single Select this can be done and even it is faster than SET. For more information on this please refer:
http://vyaskn.tripod.com/differences_between_set_and_select.htm
Mahesh
[/font]
MH-09-AM-8694
April 15, 2008 at 3:35 am
seankerr (4/14/2008)
Hi There,I trying to use the TSQL case statement to return a varchar value relating to a column. I'm passing in the value of int and it's telling me that it can't convert from int to varchar. I tried to cast the value of @Section to varchar so I could return the value but no luck.
Can someone give me a hand to fix this please?
Sean
declare @HasAnyRows int
DECLARE @COL VARCHAR(50)
DECLARE @Section int
SELECT @Section = 1
SELECT @HasAnyRows = 0
IF @HasAnyRows <= 0
BEGIN
SELECT @COL = CASE @Section
WHEN @Section = 1 THEN @COL = 'columnname'
ELSE 0
END
END
see if you are assigning value to @COL variable from table then in else part you have to type '0' instead of 0
if you do conditional assignment then the prior post from Samuel Vella is right
April 15, 2008 at 4:10 am
Mahesh Bote (4/15/2008)
No need to write separate select for assigning values for multiple variables. Within the single Select this can be done and even it is faster than SET.
[/font]
not part of this discussion
April 15, 2008 at 4:52 am
[font="Verdana"]
Samuel Vella (4/15/2008)
Mahesh Bote (4/15/2008)
No need to write separate select for assigning values for multiple variables. Within the single Select this can be done and even it is faster than SET.
[/font]
not part of this discussion
Agree, but I saw the SELECT used twice for assigning values to two variables, where it could be done in a single statement. So I thought to just update you as well as to others who are (may be) unaware with this.;)
Mahesh[/font]
MH-09-AM-8694
April 15, 2008 at 4:41 pm
HI All,
Thanks for all the opinions and answers. I think that I might have it now. If I have any dramas I will start a new thread.
thanks again,
Sean
April 15, 2008 at 4:58 pm
Sean... take a look at Books Online... most of the examples given are just flat wrong... for example...
[font="Courier New"]SELECT @COL = CASE @Section
WHEN @Section = 1 THEN 'columnname'
ELSE cast(intcolumn as varchar(x))
END
END[/font]
... should be written as ...
[font="Courier New"]SELECT @COL = CASE
WHEN @Section = 1 THEN 'columnname'
ELSE cast(intcolumn as varchar(x))
END
END[/font]
... or it can be written as ...
[font="Courier New"]SELECT @COL = CASE @Section
WHEN 1 THEN 'columnname'
ELSE cast(intcolumn as varchar(x))
END
END[/font]
... but not both combined.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 17, 2008 at 12:14 pm
Jeff Moden (4/15/2008)
Sean... take a look at Books Online... most of the examples given are just flat wrong... for example...[font="Courier New"]SELECT @COL = CASE @Section
WHEN @Section = 1 THEN 'columnname'
ELSE cast(intcolumn as varchar(x))
END
END[/font]
... should be written as ...
[font="Courier New"]SELECT @COL = CASE
WHEN @Section = 1 THEN 'columnname'
ELSE cast(intcolumn as varchar(x))
END
END[/font]
... or it can be written as ...
[font="Courier New"]SELECT @COL = CASE @Section
WHEN 1 THEN 'columnname'
ELSE cast(intcolumn as varchar(x))
END
END[/font]
... but not both combined.
Speaking of syntax, I was told a couple years back, that at least in SQL 2000 there were sometimes issues when the case statement was written as
case @variable when 'x' ...
and instead should be written
case when @variable = 'x'...
Has this been fixed in 2005? (Although since I've gotten in the habit of writing my case statements that way I probably wouldn't change anyway.)
April 17, 2008 at 12:33 pm
When all of your comparisons are a single column/variable/value, and all you're comparing is a single "is equal to" value per When, then you can use the first syntax. Otherwise, it has to be the second syntax.
For example:
case @Variable
when > 5 then...
Won't work. It's not an "is equal to" comparison.
case @Variable
when 5 then ...
when 4 then ...
when 3 then ...
when 2 then ...
when 1 then ...
else ....
end
Will work.
case
when @Variable > 5 then ...
when @Variable < -5 then ...
when @Variable = 0 or @Variable is null then ...
else ...
end
Will also work.
You can use the second syntax for equality comparisons, but you don't have to.
Out of habit and consistency, I write all of mine the second way. That way, I don't have go back and re-write it if I suddenly find that it requires a more complex comparison, I just have to add the comparison.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 17, 2008 at 12:36 pm
I was told you'd actually get back incorrect results occasionally from the first way...was just wondering if that was the case in 2005 - or maybe I was just told wrong!
April 17, 2008 at 12:41 pm
I never had incorrect results from either way, unless I had incorrect code.
Probably someone miswrote a case statement, couldn't figure out why it wasn't working, and blamed it on the code. That'd be my guess.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 23, 2008 at 5:12 pm
hi
does anyone know why this case statement is not working, i keep getting an error
Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'CASE'.
Declare @x varchar(2)
set @x = 1
CASE @x
WHEN 1 THEN
print '1: Function Call 1'
WHEN 2 THEN
print '2: Function Call 2'
else
print 'other'
END
June 23, 2008 at 5:19 pm
max.morte (6/23/2008)
hidoes anyone know why this case statement is not working, i keep getting an error
Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'CASE'.
Declare @x varchar(2)
set @x = 1
CASE @x
WHEN 1 THEN
print '1: Function Call 1'
WHEN 2 THEN
print '2: Function Call 2'
else
print 'other'
END
CASE is not a Control Flow statement. You are using it wrong. In this case you need to use nested IF statements:
Declare @x varchar(2)
set @x = 1
if @x = 1
print '1: Function Call 1'
else if @x = 2
print '2: Function Call 2'
else
print 'other'
😎
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply