Where IF logic 1

[EagleTempest]

SQL Server 2000

It's probably easier to explain my logic as I could be far off base in my coding attempt.

I want to have a query statement that when is a parameter is not null then the Where statement comes into effect.

IF Paramater is not null THEN
Parameter = @Fieldname
ELSE do use parameter at all

Is this possible or is my logic completely wrong?

 

[gmmastros]

Use this...

Where (@Parameter Is NULL OR ColumnName = @Parameter)

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[EagleTempest]

I'm thinking I need to better explain. This is what I'm trying to achieve logically.

SELECT     CREATION_DATE, ClientName, Manager, PrjNumber, ProjectName, Region, SubJobName
FROM         vPE_NewJNRPrjs
IF @RegionID IS not NULL THEN WHERE Region = @RegionID

 

[gmmastros]

Yep. That's what my code does...

Where (@RegionID IS NULL Or Region = @RegionID)

Think of this as a basic OR statement.

If either side of the OR is true, then the whole thing becomes true. So, in this case, if @RegionId IS NULL, then nothing will be filtered out. If @RegionId Is Not NULL, then only records where Region = @RegionId will be returned.

I encourage you to try this.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bborissov]

EagleTempest,
Did you tried George's example?
What he did is exactly what you want. If the parameter is not passed its value is NULL then the first part of the where will be executed. If the parameter is passed then the second part comes to place.

SELECT     CREATION_DATE, ClientName, Manager, PrjNumber, ProjectName, Region, SubJobName
FROM         vPE_NewJNRPrjs
WHERE (@RegionID IS NULL OR Region = @RegionID)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP

 

[gmmastros]

I should add another comment here.

If you have multiple parameters that does more filtering, then you need to make sure to keep the parenthesis. This is important because of the way conditional logic is applied by SQL Server.

For example, if you have another parameter called @ClientName and also have this in your where clause, you need to keep the parenthesis for the region. Ex:

SELECT CREATION_DATE, 
       ClientName, 
       Manager, 
       PrjNumber, 
       ProjectName, 
       Region, 
       SubJobName
FROM   vPE_NewJNRPrjs
WHERE  [!]([/!]@RegionID IS NULL OR Region = @RegionID[!])[/!]
       And ClientName = @ClientName

Without the parenthesis, you would NOT get the results you expect.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[EagleTempest]

I get what you are saying but I want the opposite of NULL, therefore all records.

So:
If the parameter is not passed, all records are displayed. I guess I should have said that in the first place. Sorry

 

[SQLDenis]

Did you run the code!!!!!
If you did you would have know that the code given to you is correct

example, run this

create table blaRegion (Region varchar(666))
insert blaRegion values('NJ')
insert blaRegion values('NY')
insert blaRegion values('LA')
insert blaRegion values('AA')
insert blaRegion values('AV')
insert blaRegion values('DE')
insert blaRegion values('DD')

declare @RegionID varchar(666)

SELECT     *
FROM        blaRegion
WHERE (@RegionID IS NULL OR Region = @RegionID)

declare @RegionID varchar(666)
select @RegionID ='NJ'
SELECT     *
FROM        blaRegion
WHERE (@RegionID IS NULL OR Region = @RegionID)

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[gmmastros]

I understand. But, I think you do not yet fully appreciate how this functionality works. So, I've come up with some sample data and a query that shows you how this works.

Declare @Test Table(Id int, RegionId Int)

Insert Into @Test Values(1, 1)
Insert Into @Test Values(2, 1)
Insert Into @Test Values(3, 2)
Insert Into @Test Values(4, 2)
Insert Into @Test Values(5, 3)

Declare @RegionId Int
Set @RegionId = NULL
[green]-- Set @RegionId = 1[/green]

Select *
From   @Test
Where  (@RegionId Is NULL OR RegionId = @RegionId)

This sample uses a table variable, which means you can copy/paste this to a query analyzer window and run it as is.

Notice the line that is... Set @RegionId = NULL
When @RegionId is NULL, all records will be returned. If you uncomment the green line (Set @RegionId = 1) and run it again, you will see that only those records where RegionId = 1 are returned.

Please, copy/paste this to query analyzer and play around with it a little. I think you'll be pleasantly surprised.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[EagleTempest]

Sorry for the confusion. The Query Builder shows a different version of the SQL statement than in the TableAdapter Query Configuration. Yes your code works. The Wizard was working against me.

Thanks for all your help!!!