Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Anabik
Starting Member
9 Posts |
 Posted - 2012-10-04 : 02:46:33
|
Can someone please let me know whats wrong in the query given below 
declare @strPmtModeId varchar(10),
@LeftInt Smallint,
@RightInt Smallint
set @strPmtModeId = '1,18'
set @LeftInt = Convert(Smallint,Left(@strPmtModeId,1))
set @RightInt = Convert(Smallint,Right(@strPmtModeId,2))
select * from tbl_Pmt_Mode
where (isnull(Pmt_Mode_Id,99) in
case
when Convert(SmallInt,Len(@strPmtModeId)) > 2
then
(@LeftInt,@RightInt))
or
Convert(SmallInt,@strPmtModeId) is null)
else
(Convert(SmallInt,@strPmtModeId))) or Convert(SmallInt,@strPmtModeId) is null)
end
Thanks. |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-10-04 : 07:06:03
|
I didn't quite follow the logic you are trying to implement, but SQL Server does not like the way you are using the case expression. Perhaps something like this?WHERE
( CONVERT(SMALLINT, LEN(@strPmtModeId)) > 2 AND ISNULL(Pmt_Mode_Id, 99) IN (@LeftInt, @RightInt) )
OR
@strPmtModeId IS NULL
--- OR ???
|
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-10-04 : 10:49:54
|
i think all of your conditions can be simplified into below
where ',' + @strPmtModeId + ',' like '%,' + cast(isnull(Pmt_Mode_Id,99) as varchar(3)) + ',%'
or @strPmtModeId is null
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2012-10-05 : 08:55:47
|
a) the IN clause needs to be followed by ()
b) the CASE statement is not terminated properly. The ELSE portion is outside of a pair of brackets.
c) the OR clause also seems to be in the wrong place....especially in relation to the CASE statement.
start with a simple condition and then work upwards adding more complexity as you go along. it looks like you wrote it all in one go and misplaced some brackets.
|
|
|
|
|
|
|
Incorrect syntax near the keyword 'case'.