Yet Another Challenge

[theChaz]

Using conditional statements within a SQL query are just killing me.

I want to add an AND clause to a query, conditional on the value of a parameter. The code I currently have is like this:

DECLARE @ad varchar(50)
SET @ad = NULL
SELECT tblPropertyData.propStreet, tblPropertyData.propCity, tblPropertyData.propState, tblPropertyData.propZip, tblInfoData.PTFileNumber, tblSaleData.OrigSaleDate, tblInfoData.Status, tblInfoData.OrigGrantor
FROM tblInfoData INNER JOIN tblPropertyData ON tblInfoData.PTFileNumber = tblPropertyData.PTFileNumber INNER JOIN tblSaleData ON tblInfoData.PTFileNumber = tblSaleData.PTFileNumber
WHERE     (1 = 1)
IF @ad IS NOT NULL
	AND tblPropertyData.propStreet = @ad

This code throws an error on the last line. It reads, "Incorrect syntax near 'AND tblPropertyData.propStreet = @ad'.".

This is stumping me. I'm taking the code from a Murach book, and my coding syntax seems close, if not identical, to the books. I can't figure this out.

Help me or shoot me please.

[nosepicker]

First of all, you are probably getting an error because you have an IF statement without a statement following it (or a series of statements within a BEGIN ... END structure). Secondly, you are referencing a column (tblPropertyData.propStreet) within an IF statement without using a SELECT statement. SQL will not know which value of "tblPropertyData.propStreet" you are referring to.

What are you trying to accomplish exactly? Are you trying to do something based on whether some value of tblPropertyData.propStreet equals @ad? If so, you can do something like this:

IF @ad IS NOT NULL AND EXISTS (SELECT 1 FROM tblPropertyData WHERE propStreet = @ad)
BEGIN ...

[theChaz]

My question here is based on both a mission-critical data need and a desire to push the boundaries of my skill with SQL.

This query would be part of a stored procedure and would be utilized by an asp.net (C#) app. It is supposed to run a keyword-type search on a specific table and return rows that match.

It would be called by a form action that would include between one and four different form fields - all textboxes. My thinking is that because of that, some conditional logic in the query based on what form fields are available is called for. put another way, if textbox 1 one has a value but textboxes 2,3 & 4 dont, conditional logic within the SP would sort out what AND clause(s) to use.

At least, that's what I would like it to do.

I'm not attempting to return value. I want to be able to modify the query language itself to ultimately change the result set depending on user input.

I suppose I could approach this differently, by using C# sharp to parse the data, but if I could devise a sp to do the same thing, that would, in my mind, be much better.

Hell, nosepicker, I don't even know for certain if what I want to do is even remotely possible .

I hope that made some sense.

</chaz>

First of all, you are probably getting an error because you have an IF statement without a statement following it (or a series of statements within a BEGIN ... END structure). Secondly, you are referencing a column (tblPropertyData.propStreet) within an IF statement without using a SELECT statement. SQL will not know which value of "tblPropertyData.propStreet" you are referring to.

What are you trying to accomplish exactly? Are you trying to do something based on whether some value of tblPropertyData.propStreet equals @ad? If so, you can do something like this:

IF @ad IS NOT NULL AND EXISTS (SELECT 1 FROM tblPropertyData WHERE propStreet = @ad)
BEGIN ...

[nosepicker]

OK, I think I understand now. You can use a CASE statement to hopefully accomplish some of the things you want to do, but there are limitations. For example, you can do this:

DECLARE @ad varchar(50)
SET @ad = NULL

SELECT tblPropertyData.propStreet, tblPropertyData.propCity, tblPropertyData.propState, tblPropertyData.propZip, tblInfoData.PTFileNumber, tblSaleData.OrigSaleDate, tblInfoData.Status, tblInfoData.OrigGrantor
FROM tblInfoData INNER JOIN tblPropertyData ON tblInfoData.PTFileNumber = tblPropertyData.PTFileNumber INNER JOIN tblSaleData ON tblInfoData.PTFileNumber = tblSaleData.PTFileNumber
WHERE (1 = 1)
AND tblPropertyData.propStreet =
CASE WHEN @ad IS NOT NULL THEN @ad
ELSE ...
END

[theChaz]

OK, I think I understand now. You can use a CASE statement to hopefully accomplish some of the things you want to do, but there are limitations. For example, you can do this:

DECLARE @ad varchar(50)
SET @ad = NULL

SELECT tblPropertyData.propStreet, tblPropertyData.propCity, tblPropertyData.propState, tblPropertyData.propZip, tblInfoData.PTFileNumber, tblSaleData.OrigSaleDate, tblInfoData.Status, tblInfoData.OrigGrantor
FROM tblInfoData INNER JOIN tblPropertyData ON tblInfoData.PTFileNumber = tblPropertyData.PTFileNumber INNER JOIN tblSaleData ON tblInfoData.PTFileNumber = tblSaleData.PTFileNumber
WHERE (1 = 1)
AND tblPropertyData.propStreet =
CASE WHEN @ad IS NOT NULL THEN @ad
ELSE ...
END

Okay, that makes sense.

But then is it possible to do this:

CASE WHEN @ad IS NOT NULL THEN AND tblPropertyData.propStreet = @ad

What I want/need to do is turn on/off the AND clauses for propStreet,propCity,propState and propZip. I know that I can change the values of the columns using conditional logic, but my hope was that it could be done for the entire clause.

It's not looking too good, is it?

[nosepicker]

No, as far as I know you cannot do it directly like that. However, I can think of a couple of alternatives.

You can build a dynamic SQL statement like this:

DECLARE @ad varchar(50)
SET @ad = NULL
DECLARE @str varchar(1000)

SET @str = 'SELECT tblPropertyData.propStreet, tblPropertyData.propCity, tblPropertyData.propState, tblPropertyData.propZip, tblInfoData.PTFileNumber, tblSaleData.OrigSaleDate, tblInfoData.Status, tblInfoData.OrigGrantor
FROM tblInfoData INNER JOIN tblPropertyData ON tblInfoData.PTFileNumber = tblPropertyData.PTFileNumber INNER JOIN tblSaleData ON tblInfoData.PTFileNumber = tblSaleData.PTFileNumber
WHERE (1 = 1)'
+
CASE WHEN @ad IS NOT NULL THEN ' AND tblPropertyData.propStreet = ''' + @ad + ''''
WHEN @city IS NOT NULL THEN ' AND tblPropertyData.propCity = ''' + @city + ''''
...
END

EXEC(@str)


Another way is to do something like this, if your data will allow:

SELECT tblPropertyData.propStreet, tblPropertyData.propCity, tblPropertyData.propState, tblPropertyData.propZip, tblInfoData.PTFileNumber, tblSaleData.OrigSaleDate, tblInfoData.Status, tblInfoData.OrigGrantor
FROM tblInfoData INNER JOIN tblPropertyData ON tblInfoData.PTFileNumber = tblPropertyData.PTFileNumber INNER JOIN tblSaleData ON tblInfoData.PTFileNumber = tblSaleData.PTFileNumber
WHERE (1 = 1)
AND (
tblPropertyData.propStreet =
CASE WHEN @ad IS NOT NULL THEN @ad ELSE 'zzzzzzzz' END
OR CASE WHEN @city IS NOT NULL THEN @city ELSE 'zzzzzzzz' END
etc.
)

You can substitute 'zzzzzzzz' for any other value you are sure won't occur within your data, or even NULL if you don't have NULLs in the data.

See if either of these will work for you.

[emilg]

2 remarks:

Firstly, you can use

CASE WHEN condition1 AND condition2 THEN whatever

and eliminate the need for dynamic SQL.

Secondly, the statement

CASE WHEN @ad IS NOT NULL THEN @ad 
   ELSE 'xxx'

has the following T-SQL equivalent function

isnull(@ad,'xxx')

Tks