Using Case in a Where Clause ???
-
I get this error when trying to execute the following code:
Server: Msg 245, Level 16, State 1, Line 7
Syntax error converting the nvarchar value 'Nancy' to a column of data type int.
DECLARE @Name varchar(30), @Firstname varchar(30), @ID int
SET @Name = 'y' -- 'y' = get info by firstname, 'n' = get info by employeeid
SET @Firstname = 'a'
SET @ID = 1
SELECT *
FROM northwind.dbo.employees
WHERE CASE
WHEN @Name = 'y' THEN FirstName -- (Firstname is nvarchar)
WHEN @Name = 'n' THEN EmployeeID --(EmployeeID is int)
END
like
CASE
WHEN @Name = 'y' THEN @Firstname + '%'
WHEN @Name = 'n' THEN @ID
END
If I "SET @vName = 'n'" the code executes and returns the correct results. It seems like the first case in CASE never evaluates to true. However I have found this only happens when the possible columns you are using as parameters are of a different type. The next two examples work whether it is set to 'n' or 'y' (notice both possible columns are of the same type).
This DOES work:
DECLARE @Name varchar(30), @Firstname varchar(30), @Lastname varchar(30)
SET @Name = 'y' -- 'y' = get info by firstname, 'n' = get info by lastname
SET @Firstname = 'a'
SET @Lastname = 'd'
SELECT *
FROM northwind.dbo.employees
WHERE CASE
WHEN @Name = 'y' THEN FirstName -- (Firstname is nvarchar)
WHEN @Name = 'n' THEN LastName --(Lastname is nvarchar)
END
like
CASE
WHEN @Name = 'y' THEN @Firstname + '%'
WHEN @Name = 'n' THEN @Lastname + '%'
END
and so DOES this:
DECLARE @ID varchar(30), @EmployeeID int, @ReportsTo int
SET @ID = 'y' -- 'y' = get info by id, 'n' = get info by reports to
SET @EmployeeID = 1
SET @ReportsTo = 5
SELECT *
FROM northwind.dbo.employees
WHERE CASE
WHEN @ID = 'y' THEN EmployeeID -- (EmployeeID is int)
WHEN @ID = 'n' THEN ReportsTo --(ReportsTo is int)
END
like
CASE
WHEN @ID = 'y' THEN @EmployeeID
WHEN @ID = 'n' THEN @ReportsTo
END
Has anyone come across this before or know a solution to it? THANKS!
-
I believe the mixing of varchar with integer in the first case is your problem. If you try this with @ID as a varchar, it should work.
I wasn't born stupid - I had to study.
-
Thanks for the reply. I changed my code to:
/***************************************/
DECLARE @Name varchar(30), @Firstname varchar(30), @ID varchar
SET @Name = 'y' -- 'y' = get info by firstname, 'n' = get info by employeeid
SET @Firstname = 'a'
SET @ID = '1'
SELECT *
FROM northwind.dbo.employees
WHERE CASE
WHEN @Name = 'y' THEN FirstName -- (Firstname is nvarchar)
WHEN @Name = 'n' THEN EmployeeID --(EmployeeID is int)
END
like
CASE
WHEN @Name = 'y' THEN @Firstname + '%'
WHEN @Name = 'n' THEN @ID
END
/***********************************************/
But unfortunately I still receive the same error, even with @ID as a varchar and it's value as 1 or '1'. To me it seems the problem has more to do with the column types rather than the variable types. Thanks for any help.
-
You might try the following... I believe the error you are getting occurs when the query is parsed...
DECLARE @Name varchar(30), @Firstname varchar(30), @ID int
SET @Name = 'y' -- 'y' = get info by firstname, 'n' = get info by employeeid
SET @Firstname = 'a'
SET @ID = 1
if @name = 'y'
begin
set @firstname = @firstname + '%'
SELECT *
FROM
northwind.dbo.employees
WHERE
FirstName like @Firstname
end
else -- @name should be 'n'...
begin
-
sorry about that last aborted post...continues...
begin
select *
FROM
northwind.dbo.employees
WHERE
employeeID = @id
end
-
Thanks for the suggestion. That is how I originally wrote the query but unfortunately in my real-life case the variable that @name corresponds to has about 18 different possibilities (not just 'y' or 'n') and I am performing around 30 selects, updates, inserts, etc in my stored procedure. When expanded out into an 'if -then' structure, the stored procedure becomes completely unmanageable from a future editing aspect. I was just hoping there was some clever way to get around it with minimal lines of code.
THANKS!
-
This is what BOL says about the CASE expression:
Arguments
input_expression
Is the expression evaluated when using the simple CASE format. input_expression is any valid Microsoft® SQL Server™ expression.
WHEN when_expression
Is a simple expression to which input_expression is compared when using the simple CASE format. when_expression is any valid SQL Server expression. The data types of input_expression and each when_expression must be the same or must be an implicit conversion.
Obviously this is not the case in your example. I'm no fan of dynamic sql, but in cases like yours this approach might prove superior. Have a look here:
http://www.sommarskog.se/dyn-search.html
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Thanks Frank. I was trying to avoid dynamic sql myself but that looks like the route I have to take. Thanks everyone for their help.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply