Hi Folks,

Is it possible to base case statement on one of the stored procedure parameters. Answer should be yes, but I can't seem to get my query, which uses case stmt in where clause, working:

I have a stored procedure with a following parameter:

@geography varchar,

my where clause looks like:

and comp.GeoFocus =
         case @geography
         when 'U.S.' then 'U.S.'
         when 'Asia' then 'Asia'
         when 'Europe' then "'Western Europe', 'Eastern Europe'"
         when 'Global' then 'Global'
         when 'ROW' then "'Non-U.S.', 'Latin America'"
         else comp.Geofocus
         end

It seems like it always execute else part here irrespective of value of @geography. Any ideas as to how could I get it working.

I'm not sure you can do a case statement like that (meaning, combine a variable and column). For example, i tried the following:

DECLARE @var varchar(20), @val varchar(20)
SET @var = 'Global'

SELECT @val =
         case
         when @var = 'U.S.' then 'U.S.'
         when 'Asia' then 'Asia'
         when 'Europe' then "'Western Europe', 'Eastern Europe'"
         when @var = 'Global' then 'Global'
         when 'ROW' then "'Non-U.S.', 'Latin America'"
         else @var
         end

print @val

That didn't work. The only way to successfully get it to work was to do something like:

SELECT @val =
         case
         when @var = 'U.S.' then 'U.S.'
         when @var = 'Asia' then 'Asia'
         when @var = 'Europe' then "'Western Europe', 'Eastern Europe'"
         when @var = 'Global' then 'Global'
         when @var = 'ROW' then "'Non-U.S.', 'Latin America'"
         else @var
         end

OR

         case
         when 'U.S.' = 'U.S.' then 'U.S.'
         when 'Asia' then 'Asia'
         when 'Europe' then "'Western Europe', 'Eastern Europe'"
         when 'Global' = 'Global' then 'Global'
         when 'ROW' then "'Non-U.S.', 'Latin America'"
         else comp.Geofocus
         end

Hope this helps...

Scott Klein
Author - Professional SQL Server 2005 XML
http://www.wrox.com/WileyCDA/WroxTit...764597922.html

You have to specify the length of your @geography parameter because if you don't, it defaults to a length of 1. So even if you pass 'U.S.' to your SP, the SP will only read it as 'U' and it won't match any of your criteria except for your else clause.

SQL Server Helper
How well do you know SQL? Find out with the free test assessment from SQL Server Helper!!!
http://www.sql-server-helper.com/free-test/default.aspx

Got a SQL Server Question? Ask us here: http://www.sql-server-helper.com/forums/default.asp

vghiya,

Yes, looks like variable declaration is the culprit here as sshelper says. Did you miss the length of the variable by any chance?

_________________________
- Vijay G
Strive for Perfection