case when problems 1

[ktucci]

anybody have any idea is wrong with this case when statement...thanks
---------------------------
this is the error i get

Server: Msg 245, Level 16, State 1, Line 2
Syntax error converting the varchar value 'and orders.complexorder = 1' to a column of data type int.
---------------------------

where
0=0 +
CASE 'complex' When 'complex' Then 'and orders.complexorder = 1'

Else ''
End

 

[foxdev]

The core problem is that you are trying to concatenate a number (zero) with a character string. I'm sure what you are trying to do, so I can't offer any alternative. Robert Bradley
Sr. DBA, some big company
cheap prints and oil paintings:

http://www.lowcostprints.com

 

[tlbroadbent]

I'm assuming something here that may not be correct. Posting the entire query would be helpful.

It appears you are trying to build the query dynamically. If this is the case, you must build a query string and execute the string using sp_executesql or Execute.

Declare @sql varchar(1024)

Set @sql='Select * From Table1 Where 0=0' +
Case 'complex'
When 'complex' Then 'And orders.complexorder = 1'
Else ''
End

Execute(@sql)

Question: Is 'complex' supposed to be a literal, a variable or a column? The syntax needs to be changed if a variable or column rather than literal as you have in your example. Terry

http://members.home.net/tlbroadbent/prog.htm

------------------------------------
Experience is the hardest kind of teacher. It gives you the test first, and the lesson afterward.

 

[ktucci]

thanks a million...that was the problem