January 30, 2008 at 2:42 am
Hi.
I have a stored procedure like this
CREATE PROCEDURE Test
@Param1 AS INT = NULL,
@Param2 AS INT = NULL
AS
SET NOCOUNT ON
SELECT col1, col2
FROM table1
WHERE col3 = ISNULL(@Param1, col3)
AND col4 = ISNULL(@Param2, col3)
But i seems that it won't use indexes because ISNULL is a function.
Other ways to write the code that I can think of:
Based on which of the two parameters have a value I can write four variants of the SELECT.
Write dynamic sql.
Is there a way to keep one sql and still use indexes and avoid use of dynamic sql?
Thanks,
erb
January 30, 2008 at 2:46 am
Mistake, the sql should be
CREATE PROCEDURE Test
@Param1 AS INT = NULL,
@Param2 AS INT = NULL
AS
SET NOCOUNT ON
SELECT col1,
col2
FROM table1
WHERE col3 = ISNULL(@Param1, col3)
AND col4 = ISNULL(@Param2, col4)
Thanks,
erb
January 30, 2008 at 2:56 am
A function will only stop an index being used if it's applied to the column, not a parameter or constant.
John
January 30, 2008 at 3:04 am
Thanks for the response.
I want the clause
col3 = ISNULL(@Param1, col3)
to be always true if @Param1 is NULL
and
col4 = ISNULL(@Param2, col4)
to be always true if @Param2 is NULL.
If both @Param1 and @Param2 is NULL, I want all rows to be returned.
erb
January 30, 2008 at 3:10 am
So you're only testing for the NULLness of @Param1 and @Param2? That doesn't relate to the columns at all, and so you're either going to get all the rows or none of them. If that's what you want, then use the COALESCE FUNCTION:
WHERE COALESCE(@Param1, @Param2) IS NULL
John
January 30, 2008 at 3:33 am
Well if @Param1 contains a value, I want col3 to be tested against that value
col3 = @Param1
Otherwise i get col3 = col3 which is always true.
erb
January 30, 2008 at 4:04 am
WHERE (col3 = @Param1 OR @Param1 IS NULL)
_____________
Code for TallyGenerator
January 30, 2008 at 4:04 am
Then I think your original code should be OK. But check the execution plan to see whether it is using any index that you expect it to, and if not, change it to something like this and try again:
WHERE (col3 = @Param1 OR @Param1 IS NULL)
AND (col4 = @Param2 OR @Param2 IS NULL)
John
January 30, 2008 at 6:19 am
I tried your last suggestion and it doesn't seem to be very efficient.
While using col3 = @Param1 gives me a cost of 2, the code (col3 = @Param1 OR @Param1 IS NULL) shows a cost of 1500.
erb
January 30, 2008 at 6:26 am
Yes, but you're not comparing like for like. You need to compare the code that Sergiy and I suggested against the code in your original post.
John
January 30, 2008 at 6:53 am
Seems like I have been unclear about my problem so I try to explain it differently.
I can write my procedure something like this :
CREATE PROCEDURE Test
@Param1 AS INT = NULL,
@Param2 AS INT = NULL
AS
IF @Param1 IS NOT NULL AND @Param2 IS NOT NULL THEN
SELECT col1,
col2
FROM table1
WHERE col3 = @Param1
AND col4 = @Param2
ELSE IF @Param1 IS NOT NULL AND @Param2 IS NULL THEN
SELECT col1,
col2
FROM table1
WHERE col3 = @Param1
ELSE IF @Param1 IS NULL AND @Param2 IS NOT NULL THEN
SELECT col1,
col2
FROM table1
WHERE col4 = @Param2
ELSE
SELECT col1,
col2
FROM table1
This method gives me four SELECT's to maintain but it is relatively fast and one SELECT gives me a cost of 2. If I have 5 in-parameters that can be NULL, I get 25 IF's with 25 SELECT's and the code gets hard to maintain.
It vould be much better to have one select that could cope with all the in-parameters. But on one condition not to loose performance.
I could use dynamic sql but I'm not sure about performance compared to the procedure above.
erb
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply