Need help with SQL Statement checking for nulls

[fperri]

Hi,

I have a table with over 20 columns. Seven of these of these columns correspond to a filter on a search form. Based on what is selected on the search form, and what is in these seven fields in the table, it indicates whether or not I pull the record. Thing is....is that not all the records use the same combination of these seven fields. One might only use the first two and the other five will be null, or one might use the middle three and the other four will be null. However, if any of these fields match up to its corresponding filter on the form, even if the others are null (because if a field is NULL it applies to all), I need to pull it.

I started out trying to do this in VB code and executing a regular select statment for each combination, but I quickly realized that this is not good as it will mean a ton of code to check for each field combination that is possible.

So, I'm trying to put together a single SQL Statment that I will execute on the SQL Server that will check if each of these fields is null before trying to do a comparrison on it. I don't know if this can be done and I am very new to the syntax for SQL Server queries. Here is what I have so far just trying to do the check on one of the columns;

SELECT [RecordNumber]
      ,[Lender]
      ,[LoanName]
      ,[Adjustment]
FROM [Website].[dbo].[Adjustments]
WHERE [Website].[dbo].[Adjustments].[Lender] = 'Test'
AND [Website].[dbo].[Adjustments].[LoanName] = 'Testing'
IF [Website].[dbo].[Adjustments].[Units] IS NOT NULL THEN
    AND [Website].[dbo].[Adjustments].[Units] = 2
 

If I try to run this I get a message saying;

incorrect synax near 'THEN'

So I remove that and get

incorrect syntax near '2'

Is this possible or am I way out in left field?

[ck9663]

Hi,

I have a table with over 20 columns. Seven of these of these columns correspond to a filter on a search form. Based on what is selected on the search form, and what is in these seven fields in the table, it indicates whether or not I pull the record. Thing is....is that not all the records use the same combination of these seven fields. One might only use the first two and the other five will be null, or one might use the middle three and the other four will be null. However, if any of these fields match up to its corresponding filter on the form, even if the others are null (because if a field is NULL it applies to all), I need to pull it.

I started out trying to do this in VB code and executing a regular select statment for each combination, but I quickly realized that this is not good as it will mean a ton of code to check for each field combination that is possible.

So, I'm trying to put together a single SQL Statment that I will execute on the SQL Server that will check if each of these fields is null before trying to do a comparrison on it. I don't know if this can be done and I am very new to the syntax for SQL Server queries. Here is what I have so far just trying to do the check on one of the columns;

SELECT [RecordNumber]
      ,[Lender]
      ,[LoanName]
      ,[Adjustment]
FROM [Website].[dbo].[Adjustments]
WHERE [Website].[dbo].[Adjustments].[Lender] = 'Test'
AND [Website].[dbo].[Adjustments].[LoanName] = 'Testing'
IF [Website].[dbo].[Adjustments].[Units] IS NOT NULL THEN
    AND [Website].[dbo].[Adjustments].[Units] = 2
 

If I try to run this I get a message saying;

incorrect synax near 'THEN'

So I remove that and get

incorrect syntax near '2'

Is this possible or am I way out in left field?

first your error. sql server IF condition does not have the THEN (read here.

about your search condition, can the operator OR handle it?

-CK

[fperri]

first your error. sql server IF condition does not have the THEN (read here.

about your search condition, can the operator OR handle it?

-CK

Never thought about OR, I'll give it a try.