February 4, 2009 at 9:34 am
I have a stored procedure that allows for nulls. When selecting data from my tables I normally do something like
Create Procedure DoSomething(@Status Char(1) = Null)
if (@Status Is Null)
Begin
Select Key, Description, Status
From Table
Order By Description
End
Else
Begin
Select Key, Description, Status
From Table
Where Status = @status
Order By Description
End
I know there my be a better way to do this so that I don't have to repeat the Select Clause. I just can't seem to wrap my brain around the proper Where clause.
Thanks
ps. I know the syntax above is not correct. I only wanted to show the relevant code.
February 4, 2009 at 9:40 am
meichner (2/4/2009)
I know there my be a better way to do this so that I don't have to repeat the Select Clause.
There is, but it tends to have interesting performance problems. The way you're currently doing it also has performance problems in the form of parameter sniffing.
The best way to do that is to have each of the selects in it's own procedure and call the relevant procedure from the if statement.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 4, 2009 at 10:16 am
GilaMonster (2/4/2009)
meichner (2/4/2009)
I know there my be a better way to do this so that I don't have to repeat the Select Clause.There is, but it tends to have interesting performance problems. The way you're currently doing it also has performance problems in the form of parameter sniffing.
The best way to do that is to have each of the selects in it's own procedure and call the relevant procedure from the if statement.
I am not so worried about performance in this case because the procedure in question is used to load static data. The table currently has about 20 rows and will not grow too much (if any) over time.
Therefore I was hoping to combine the separate if statements into one.
Thanks
February 4, 2009 at 11:19 am
Then this should do it.
WHERE Status = @status OR @status IS NULL
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 4, 2009 at 11:38 am
I had that solution but something bothered me about it. Since I am not a SQL expert I didn't trust myself.
Does this mean that if I changed the where clause to:
WHERE @status IS NULL
that in essence the where class does not get run and that all rows are selected from the table (which is what I want when no status is supplied to the stored procedure)?
What confuses me is that normally the Where Clause is comparing a value against a field in the table. In this case the where clause doesn't reference anything in the table.
Thanks so much for the help.
February 4, 2009 at 12:05 pm
meichner (2/4/2009)
that in essence the where class does not get run and that all rows are selected from the table (which is what I want when no status is supplied to the stored procedure)?
Oh, it'll get run, but since @status is a parameter and has a single value, that comparison will return true or false for all the rows, depending on the value of @status. So you'll either get the entire table, or 0 rows.
What confuses me is that normally the Where Clause is comparing a value against a field in the table. In this case the where clause doesn't reference anything in the table.
Doesn't have to be. The predicates in the where just have to be logical comparisons. They usually are based on the table, but that's not required.
Try selecting from any table with the following two where clauses and you'll see the behaviour.
WHERE 1=1
WHERE 1=0
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 4, 2009 at 12:08 pm
I can't speak to the 'performance issue' someone raised above, but I can tell you this ... I use the method described all the time, and it does do exactly what you want.
Where clauses do not have to reference a field in the table. In this case, when your parameter is NULL, what you really want to happen is for there to be no comparisons going on, in effect, no 'where' clause to be enforced. But since you need the Where clause there for cases where the parameter IS passed in, what you are doing with the 'OR @status Is Null' is forcing every row to be evaluated as 'True' by your Where clause ... whenever a Null is passed in as @status.
February 4, 2009 at 1:05 pm
GilaMonster (2/4/2009)
meichner (2/4/2009)
that in essence the where class does not get run and that all rows are selected from the table (which is what I want when no status is supplied to the stored procedure)?Oh, it'll get run, but since @status is a parameter and has a single value, that comparison will return true or false for all the rows, depending on the value of @status. So you'll either get the entire table, or 0 rows.
What confuses me is that normally the Where Clause is comparing a value against a field in the table. In this case the where clause doesn't reference anything in the table.
Doesn't have to be. The predicates in the where just have to be logical comparisons. They usually are based on the table, but that's not required.
Try selecting from any table with the following two where clauses and you'll see the behaviour.
WHERE 1=1
WHERE 1=0
Thanks so much for all the help.
February 4, 2009 at 1:07 pm
bvaljalo (2/4/2009)
I can't speak to the 'performance issue' someone raised above, but I can tell you this ... I use the method described all the time, and it does do exactly what you want.Where clauses do not have to reference a field in the table. In this case, when your parameter is NULL, what you really want to happen is for there to be no comparisons going on, in effect, no 'where' clause to be enforced. But since you need the Where clause there for cases where the parameter IS passed in, what you are doing with the 'OR @status Is Null' is forcing every row to be evaluated as 'True' by your Where clause ... whenever a Null is passed in as @status.
Thanks. Between you and GilaMonster I am now clear on what to do.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply