September 8, 2004 at 11:12 am
This should be easy. I have need to write a single stored proc that will use or not use the Where and Order By clauses based on paramater values. I want to use a case statement, but I don't seem to be able to do that. I can do it with If statements, but it makes the code look like this:
create procedure usp_CM_Get_Info
(
@sort int = 0,
@UPPER_DESCRIPTION varchar(60) = ''
)
as
Set Nocount On
if @UPPER_DESCRIPTION <> ''
begin
if @sort <> 0
begin
Select SITE_ID, [DESCRIPTION], UPPER_DESCRIPTION,
UPDATED_BY, LAST_UPDATE, ACTIVE
From dbo.LU_SITE
Where UPPER_DESCRIPTION = @UPPER_DESCRIPTION
Order By [SEQUENCE]
end
else
begin
Select SITE_ID, [DESCRIPTION], UPPER_DESCRIPTION,
UPDATED_BY, LAST_UPDATE, ACTIVE
From dbo.LU_SITE
Where UPPER_DESCRIPTION = @UPPER_DESCRIPTION
end
end
else
begin
if @sort <> 0
Select SITE_ID, [DESCRIPTION], UPPER_DESCRIPTION,
UPDATED_BY, LAST_UPDATE, ACTIVE
From dbo.LU_SITE
Order By [SEQUENCE]
end
else
begin
Select SITE_ID, [DESCRIPTION], UPPER_DESCRIPTION,
UPDATED_BY, LAST_UPDATE, ACTIVE
From dbo.LU_SITE
From dbo.LU_SITE
end
end
GO
Any ideas and best practices are always appreciated.
WABALUBADUBDUB
September 8, 2004 at 11:53 am
Here is a solution for the WHERE clause using a variable turned off or on. The solution for the ORDER BY might be a problem if you don't want an extra column output in your query (Seq).
create procedure usp_CM_Get_Info
(
@sort int = 0,
@UPPER_DESCRIPTION varchar(60) = ''
)
as
Set Nocount On
declare @trueFlag bit
if @UPPER_DESCRIPTION = '' set @trueFlag = 1
Select SITE_ID, [DESCRIPTION], UPPER_DESCRIPTION,
UPDATED_BY, LAST_UPDATE, ACTIVE,
Seq = case when @sort <> '' then [SEQUENCE] else 0 end
From dbo.LU_SITE
Where (UPPER_DESCRIPTION = @UPPER_DESCRIPTION or @trueFlag=1)
Order By Seq
go
September 9, 2004 at 5:06 am
I've used the above technique (slightly modified below) lots of times for WHERE clauses. The ORDER BY clause is more difficult. If you just have SORT or DON'T SORT and the columns are always the same in the SORT version then you just use an IF/ELSE like you are now. If you need to be able to have varying columns in the ORDER BY clause then you will need to use dynamic SQL.
My version of the above (assuming you don't want additional columns):
create procedure usp_CM_Get_Info
(
@sort int = 0,
@UPPER_DESCRIPTION varchar(60) = ''
)
as
Set Nocount On
UPDATED_BY, LAST_UPDATE, ACTIVE
Where (UPPER_DESCRIPTION = @UPPER_DESCRIPTION or @UPPER_DESCRIPTION = '')
ELSE
UPDATED_BY, LAST_UPDATE, ACTIVE
Where (UPPER_DESCRIPTION = @UPPER_DESCRIPTION or @UPPER_DESCRIPTION = '')
go
September 9, 2004 at 6:55 am
I prefer using 'like' in the where clause and setting the parameter equal to % when the user wants to bypass it. The order by would have to be handled conditionally.
I am more concerned at what appears to be the use of reserved words as columns names. This is pretty high on the list of bad habits. If you reported to anyone at my company, you'd be in trouble.
September 9, 2004 at 8:04 am
There are two other possibilities for handling the order by. I've also included yet another option for dealing with the WHERE clause change, not that there is anything wrong with the other three options already presented. If you wind up with a lot of variances in the order by statements, the order by clause can be handled with a case statement or inside an IF statement similar to the above approaches. If using the IF statement approach you can avoid recoding the main query by placing it into a variable based temp table as in the following:
declare @TempRS table( SITE_ID int, [DESCRIPTION] varchar(60), UPPER_DESCRIPTION varchar(60), UPDATED_BY char(8), LAST_UPDATE datetime, ACTIVE bit, [SEQUENCE] int ) if @UPPER_DESCRIPTION = '' set @UPPER_DESCRIPTION = NULL INSERT INTO @TempRS SELECT SITE_ID, [DESCRIPTION], UPPER_DESCRIPTION, UPDATED_BY, LAST_UPDATE, ACTIVE, [SEQUENCE] FROM dbo.LU_SITE WHERE UPPER_DESCRIPTION = ISNULL( @UPPER_DESCRIPTION, UPPER_DESCRIPTION ) if @sort = 0 begin SELECT SITE_ID, [DESCRIPTION], UPPER_DESCRIPTION, UPDATED_BY, LAST_UPDATE, ACTIVE FROM @TempRS end else if @sort = 1 begin SELECT SITE_ID, [DESCRIPTION], UPPER_DESCRIPTION, UPDATED_BY, LAST_UPDATE, ACTIVE FROM @TempRS ORDER BY [SEQUENCE] end |
Alternatively you could create a second SP that returned all the data being placed in the @TempRS table above and then in the IF block select from the EXEC of the SP instead of the temp table. Note that the above also avoids returning [SEQUENCE] should you not care to have it. If you don't mind returning it SELECT * would read better.
Another option is using a case statement in the ORDER BY instead of using an outer IF structure at all. In your case the whole thing is reduced to a single SQL statement vs 2 or 4 statements.
if @UPPER_DESCRIPTION = '' set @UPPER_DESCRIPTION = NULL SELECT SITE_ID, [DESCRIPTION], UPPER_DESCRIPTION, UPDATED_BY, LAST_UPDATE, ACTIVE FROM dbo.LU_SITE WHERE UPPER_DESCRIPTION = ISNULL( @UPPER_DESCRIPTION, UPPER_DESCRIPTION ) ORDER BY case when @sort = 1 then [SEQUENCE] else '' end |
September 9, 2004 at 8:27 am
I never knew you could use a CASE statement in the ORDER BY clause! Wow! The number of complex SQL statements I can reduce (inherited from someone who knew nothing about SQL) in some of our apps is astronomical. This makes setting up stored procedures for requests where the ORDER BY is variable much simpler.
Thanks!
September 9, 2004 at 9:13 am
To be honest I didn't know you could either. I started my post for the temp table/exec thing but first decided to prove that a case didn't work in the ORDER BY and found out it did work. Since I was under the distinct impression that I had tried it and failed before, I think it may be an enhancement as of SQL 2000.
September 9, 2004 at 10:06 am
Great stuff folks. Thanks.
BTW to Sue, this is a legacy system that I am working on. I would like to be rid of the problem, but I am stuck with the column names.
WABALUBADUBDUB
September 10, 2004 at 2:55 am
Hi guys,
Have you considered using dynamic SQL? I.e., you would create a varchar variable with the query (using CASE to determine if the query would have either the WHERE or ORDER BY) and then execute it with EXECUTE or sp_executesql.
I think this is more standard solution.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply