---

Subject: [aspnet] RE: SQL GURU: IN operator with Case Statement
Date: Tue, 10 Jun 2008 09:53:21 -0700
From: Keith.Barrowswatsonwyatt.com
To: aspnetaspadvice.com

Not necessarily (though I like Penton's solution).

 

CREATE PROC myProc parm int = 0  -- Default parm to 0 if nothing is passed in

AS

BEGIN

    declare tbl TABLE ( intCodes INT )

    
 &nbsp;  IF parm <> 0 -- We got a parm passed in so use just it...

 &nbsp;  BEGIN

 &nbsp; &nbsp; &nbsp;  INSERT&nbsp;INTO tbl (intCodes) select parm -- Insert the parm for later use...
 &nbsp;  END

 &nbsp;  ELSE  -- We did not get a parm or it was passed in as 0...

 &nbsp;  BEGIN

 &nbsp; &nbsp; &nbsp;  INSERT INTO tbl (intCodes)  select 123
 &nbsp; &nbsp; &nbsp;  INSERT INTO tbl (intCodes)  select 456
 &nbsp; &nbsp; &nbsp;  INSERT INTO tbl (intCodes)  select 789

 &nbsp;  END

 &nbsp; 

 &nbsp;  -- Now, select out the data you need...

 &nbsp;  SELECT blah, blah
 &nbsp;  FROM tblBlah

 &nbsp;  INNER JOIN tbl ON tblBlah.intField = tbl.intCodes
END

 

---

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

problem with this is, if the user submits a parm, then it has to be used in the where clause.
I added a value of "0" with text "-All-" in the page drop down in the first index before the 3 hard-coded codes.&nbsp; So, that's why I'm checking for parm = 0.

---

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
(
 &nbsp; &nbsp; 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/

---

Enjoy 5 GB of free, password-protected online storage. Get Windows Live SkyDrive. --- List Settings ---
http://aspadvice.com/lists/

Notice of Confidentiality

This transmission contains information that may be confidential. It has been prepared for the sole and exclusive use of the intended recipient and on the basis agreed with that person. If you are not the intended recipient of the message (or authorized to receive it for the intended recipient), you should notify us immediately; you should delete it from your system and may not disclose its contents to anyone else.

This e-mail has come to you from Watson Wyatt & Company.
--- List Settings ---
http://aspadvice.com/lists/