help with stored procedure

Question

help with stored procedure

Viewing 10 posts - 16 through 24 (of 24 total)

You must be logged in to reply to this topic. Login to reply

bp SSCarpal Tunnel Points: 4656 More actions · Answer 1

To follow up on my last post, if you have a date criteria which you want as optional, then it is best to piece the sql statement together and then execute it.

Billy

Calvin Lawson SSChampion Points: 11030 More actions · Answer 2

zaidk hit the nail on the head; by FAR the best way to do it. Use an IsNull() to replace the variable with the column value it's compared to.

When the variable isn't null it's compared, when it is null the comparison is the column value to itself, which is practically free.

Good stuff.

cl

Signature is NULL

CtrlAltDel SSC Enthusiast Points: 100 More actions · Answer 3

The way I always do optional filters in queries is thusly:

CREATE PROCEDURE query

(

   @filter1               VARCHAR(30),

   @filter2               INT

)

AS



SELECT col1, col2

FROM table

WHERE

   col3 = COALESCE(@filter1, col3) AND

   ISNULL(col4, 0) = COALESCE(@filter2, col4, 0)

This one I've made a little more complex by allowing NULLs in col4, hence the extra value in the COALESCE function. Does anyone else use this method?

Edited by - CtrlAltDel on 10/17/2003 1:31:55 PM

lenardd Default port Points: 1435 More actions · Answer 4

CtrlAltDel,

That won't work because what if @filter1 is NULL and col3 is NULL? The SELECT won't return anything if @filter2 has a value.

Basically, if your database fields contain NULLs, you can do it like:



SELECT col1, col2

FROM table

WHERE (col3 = ISNULL(@filter1, col3) OR col3 IS NULL) AND (col4 = ISNULL(@filter2, col4) OR col4 IS NULL)

If your database fields don't contain NULLs, you can get away with just:



SELECT col1, col2

FROM table

WHERE col3 = ISNULL(@filter1, col3) AND col4 = ISNULL(@filter2, col4)

If you read my Topic Thread (ie. "OR Evaluations"), you will note that I'm still looking if there is a better way of doing optional filters.

--Lenard

bp SSCarpal Tunnel Points: 4656 More actions · Answer 5

lenardd

If you are still looking for a way of doing optional filters, what do you think of piecing the sql statement together in your stored procedure? this method will allow you to include date and number criteria (using the > and < operators). The isnull method is limited to exact or pattern matching.

The only downside to piecing the sql statement together within your stored procedure is that the resulting sql statement is not cached (although doesn't sql server cache single sql statements as well)?

Billy

lenardd Default port Points: 1435 More actions · Answer 6

BP,

Yes, I've considered using dynamic SQL. I would have gone that route if I had lots of optional filters. In my case, I only have between 2-5 and preferred the readability and maintenance of the non-dynamic way.

Maybe I'm just making a big fuss about nothing. But just seeing how others tackle a problem can really clear up one's hesitation on whether their own solution is sound.

--Lenard

Jonathan SSC-Insane Points: 20427 More actions · Answer 7

quote:
The way I always do optional filters in queries is thusly:
CREATE PROCEDURE query
(
@filter1 VARCHAR(30),
@filter2 INT
)
AS

SELECT col1, col2
FROM table
WHERE
col3 = COALESCE(@filter1, col3) AND
ISNULL(col4, 0) = COALESCE(@filter2, col4, 0)
This one I've made a little more complex by allowing NULLs in col4, hence the extra value in the COALESCE function. Does anyone else use this method?
Edited by - CtrlAltDel on 10/17/2003 1:31:55 PM

What if @filter2 is 0? Then NULL valued rows will be returned.

quote:
SELECT col1, col2FROM table
WHERE (col3 = ISNULL(@filter1, col3) OR col3 IS NULL) AND (col4 = ISNULL(@filter2, col4) OR col4 IS NULL)
...
If you read my Topic Thread (ie. "OR Evaluations"), you will note that I'm still looking if there is a better way of doing optional filters.

This is also incorrect, as it will return rows with NULL values even when the parameter is not null. That's why I was confused on your other thread.

--Jonathan

lenardd Default port Points: 1435 More actions · Answer 8

Jonathan, regarding your last statement.....

>> This is also incorrect, as it will return rows with NULL values even when the

>> arameter is not null. That's why I was confused on your other thread"

But col3 = NULL is not the same as col3 IS NULL.

So, if @filter1 is optional (ie. NULL) and the col3 field contains a NULL value, you need to add "OR col3 IS NULL" or the SELECT statement won't return anything because col3 = NULL will be FALSE. You want it to be TRUE (thus the "OR col3 IS NULL" part) so that @filter2 will return something if it evaluates to TRUE.

--Lenard

Jonathan SSC-Insane Points: 20427 More actions · Answer 9

quote:
Jonathan, regarding your last statement.....
>> This is also incorrect, as it will return rows with NULL values even when the
>> parameter is not null. That's why I was confused on your other thread"
But col3 = NULL is not the same as col3 IS NULL.
So, if @filter1 is optional (ie. NULL) and the col3 field contains a NULL value, you need to add "OR col3 IS NULL" or the SELECT statement won't return anything because col3 = NULL will be FALSE. You want it to be TRUE (thus the "OR col3 IS NULL" part) so that @filter2 will return something if it evaluates to TRUE.
--Lenard

The problem is not when your parameter is null. The problem is when the parameter is not null. Let's say your parameter is "West" and your column has values of "East", "West", "North", "South", and some NULLs. Your query will return the rows with the value of "West" and the rows with null values. I don't think that's what you intend...

--Jonathan

lenardd Default port Points: 1435 More actions · Answer 10

Ohhhhh, said the blind man and he picked up his hammer and saw.

I get it now.....thanks!

--Lenard