November 11, 2009 at 2:16 pm
We got some code from a contractor that uses a lot of dynamic SQL in order to handle different WHERE clauses. For example this simplified code allows an additional where clause to be added to the query if location <> 0, then the order by is appended.
Is there a nice way to do this without dynamic SQL ?
create PROCEDURE [dbo].[usp_Dynamic]
@location_id int = NULL
AS
declare @sql varchar(8000)
set @sql= 'select
a.location_name As [Vehicle_Location]
,c.location_name AS [Accident Location]
,c.date_incident
from VEHICLE_INCIDENT c
inner JOIN VEHICLE_LOCATION b on c.vehicle_id=b.vehicle_id
inner JOIN LOCATION a on a.vitel_location_id=b.location_id
where c.vehicle_incident_type_id=1 '
if @location_id <>0
set @sql=@sql + ' and b._location_id = ' + @location_id
if @start_date is not null
set @sql = @sql + ' and c.date_incident >= ''' + @start_date+''''
if @end_date is not null
set @sql = @sql + ' and c.date_incident <= ''' + @end_date+''''
set @sql=@sql + ' order by a.location_name,c.date_incident '
exec(@sql)
EDIT: I added the other WHERE clase statements from my second post to put it all together
November 11, 2009 at 2:32 pm
create PROCEDURE [dbo].[usp_Dynamic]
@location_id int = NULL
AS
IF @location_id<>0
select
a.location_name As [Vehicle_Location]
,c.location_name AS [Accident Location]
,c.date_incident
from VEHICLE_INCIDENT c
inner JOIN VEHICLE_LOCATION b on c.vehicle_id=b.vehicle_id
inner JOIN LOCATION a on a.vitel_location_id=b.location_id
where c.vehicle_incident_type_id=1
and b.location_id = @location_id
order by a.location_name, c.date_incident
else
select
a.location_name As [Vehicle_Location]
,c.location_name AS [Accident Location]
,c.date_incident
from VEHICLE_INCIDENT c
inner JOIN VEHICLE_LOCATION b on c.vehicle_id=b.vehicle_id
inner JOIN LOCATION a on a.vitel_location_id=b.location_id
where c.vehicle_incident_type_id=1
order by c.date_incident
GO
If this proc is called frequently then I would take one of two approaches.
1. Have a separate proc for each branch and have the existing proc call one of the two child procs depending on the condition.
2. Keep the dynamic SQL but use sp_executesql
The reason for doing so is the same in both cases. The optimum execution plan for one query is likely to be different to the optimum execution plan for the 2nd one.
Having two stored procs gets around this.
sp_executesql will have a different signature and will therefore cache two execution plans.
I would also consider wrapping the bulk of the query up in a view as it is only the WHERE clause and order by that differ.
I've changed the order by because when location_id=0 you are only going to have one location name in any case.
November 11, 2009 at 2:48 pm
Maybe I simplified the example code too much. There are actually 3 different WHERE clause variables, which complicates things:
if @location_id <>0
set @sql=@sql + ' and b._location_id = ' + @location_id
if @start_date is not null
set @sql = @sql + ' and c.date_incident >= ''' + @start_date+''''
if @end_date is not null
set @sql = @sql + ' and c.date_incident <= ''' + @end_date+''''
I was just wondering if a temp table could somehow be loaded with the variables and used as a join to get the same result ?
EDIT: I updated the OP to reflect this
November 11, 2009 at 2:59 pm
Please have a look at one of Gails great articles:
The article describes several options, analyzing pros and cons.
November 11, 2009 at 3:01 pm
Hi
To ensure a correct execution plan you should split your procedure into two different procedures. One which works with @location_id and one which doesn't. You can use your entry procedure calls the others.
Greets
Flo
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply