---

Date: Tue, 10 Jun 2008 11:56:38 -0400
From: terri.mortongmail.com
To: aspnetaspadvice.com
Subject: [aspnet] RE: SQL GURU: IN operator with Case Statement

But you could do a LEFT OUTER JOIN:

declare tbl TABLE
(
     intCodes INT
)
 
INSERT tbl select 123
INSERT tbl select 456
INSERT tbl select 789
 
SELECT blah, blah
FROM tblBlah
LEFT OUTER JOIN tbl ON tblBlah.intField = tbl.intCodes
WHERE tbl.intcodes IS NOT NULL OR param = 0

FWIW, I always recommend these great articles by Erland Sommarskog on Arrays and Lists in SQL Server:
http://www.sommarskog.se/arrays-in-sql-2005.html
http://www.sommarskog.se/arrays-in-sql-2000.html

On Tue, Jun 10, 2008 at 11:45 AM, Tim Curtin < tjc_tekhotmail.com">tjc_tekhotmail.com> wrote:

Can't join due to a parm containing a value would restrict the parm to only the 3 codes.
I got it though:
&nbsp;
WHERE (
 &nbsp; &nbsp; &nbsp;   ; &nbsp;  (parm = 0 AND intField IN (SELECT intCodes FROM tbl))
&nbsp; &nbsp; &nbsp; &nbsp;   ; &nbsp; &nbsp;  or (parm > 0 AND intField = parm)
&nbsp; &nbsp; &nbsp; &nbsp;   )

---

Subject: [aspnet] RE: SQL GURU: IN operator with Case Statement
Date: Tue, 10 Jun 2008 07:58:23 -0700
From: Keith.Barrowswatsonwyatt.com">Keith.Barrowswatsonwyatt.com
To: aspnetaspadvice.com">aspnetaspadvice.com

declare tbl TABLE
(
&nbsp; &nbsp;  intCodes INT
)

IF parm = 0

BEGIN

 &nbsp;  INSERT tbl select parm
END

ELSE

BEGIN

 &nbsp;  INSERT tbl select 123
   ; INSERT tbl select 456
   ; INSERT tbl select 789

END

 

SELECT blah, blah
FROM tblBlah

INNER JOIN tbl ON tblBlah.intField = tbl.intCodes

-kb

 

---

From: Tim Curtin [mailto: tjc_tekhotmail.com">tjc_tekhotmail.com]
Sent: Tuesday, June 10, 2008 7:55 AM
To: aspnetaspadvice.com">aspnetaspadvice.com
Subject: [aspnet] SQL GURU: IN operator with Case Statement

Hello folks,
&nbsp;
I'm trying to set the hard-coded values or the parm in a IN operator without using dynamic SQL.
 ;
I've tried a couple of different approaches.  This most recent one doesn't work
 ;
 
declare tbl TABLE
(
&nbsp; &nbsp;  intCodes INT
)
&nbsp;
INSERT tbl select 123
INSERT tbl select 456
INSERT tbl select 789
 
SELECT blah, blah
FROM tblBlah
WHERE intField IN ( &nbsp; CASE WHEN parm = 0 THEN
 ; &nbsp; &nbsp; &nbsp; &nbsp;   ; &nbsp; &nbsp; &nbsp; &nbsp;   ; &nbsp; &nbsp; &nbsp; &nbsp;  (SELECT intCodes FROM tbl)
 ; &nbsp; &nbsp; &nbsp; &nbsp;   ; &nbsp; &nbsp; &nbsp; &nbsp;   ; &nbsp; &nbsp;  ELSE
 &nbsp; &nbsp; &nbsp;   ; &nbsp; &nbsp; &nbsp; &nbsp;   ; &nbsp; &nbsp; &nbsp; &nbsp;   ; &nbsp; parm
 ; &nbsp; &nbsp; &nbsp; &nbsp;   ; &nbsp; &nbsp; &nbsp; &nbsp;   ; &nbsp; &nbsp;  END)
 ;

--- List Settings --- http://aspadvice.com/lists/