Can I Set Logical Operator Programatically? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Can I Set Logical Operator Programatically?

Author

Topic

CactusJuice
Starting Member

46 Posts

Posted - 2002-12-11 : 18:45:02

Is there a way to switch on/off an operator *without* going to dynamic SQL? I am searching an employee database accross many multiple AND optional parameters. I'm showing 2 params here but there are about 15 all together of differing types and several CSV's.

=== Code Snippet ===
DECLARE @pDepartmentID varchar(300)
DECLARE @pSecondaryDeptID varchar(300)

-- Test values
SET @pDepartmentID = '100, 03,106'
SET @pSecondaryDeptID = '502,505'

SELECT * FROM tblHumanResource h
WHERE h.DepartmentID IN (SELECT IntValue FROM dbo.CsvToInt(@pDepartmentID))
AND h.SecondaryDeptID IN (SELECT IntValue FROM dbo.CsvToInt(@pSecondaryDeptID))

=== End Snippet ===

The above works fine. HOWEVER, if there are no Secondary Departments then the query returns nothing. But I found in this case that by

AND h.SecondaryDeptID NOT IN (SELECT IntValue FROM dbo.CsvToInt(@pSecondaryDeptID))

then the query again returns results. Kind of tricking it not to check the parameter list is Null. But this only works because I'm present to change it...can it be done at run time in the Sproc?

Also notice I am using the UDF as per
http://www.sqlteam.com/item.asp?ItemID=11499

thanks,

Cameron

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-12-11 : 19:55:24

I think so. You need to allow your variable to be NULL. In your Stored Proc, Declare the variables like this:
@pDepartmentID varchar(300) = NULL,
@pSecondaryDeptID varchar(300) = NULL

I changed your AND to an OR. That may or maynot work for ya.

 
=== Code Snippet === 
DECLARE @pDepartmentID varchar(300)
DECLARE @pSecondaryDeptID varchar(300) 

-- Test values 
SET @pDepartmentID = '100, 03,106' 
SET @pSecondaryDeptID = '502,505' 

SELECT * FROM tblHumanResource h 
WHERE (h.DepartmentID IN (SELECT IntValue FROM dbo.CsvToInt(@pDepartmentID)) OR @pDepartmentID IS NULL)
OR (h.SecondaryDeptID IN (SELECT IntValue FROM dbo.CsvToInt(@pSecondaryDeptID)) OR @pSecondaryDeptID IS NULL)

=== End Snippet ===

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

CactusJuice
Starting Member

46 Posts

Posted - 2002-12-11 : 20:45:41

Yep you go it! I actually just figured out the same thing when I came accross this thread a few minutes ago.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=6003

Thanks for your help.

Cameron

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources