bad sql or dynamic where

Last Post 06 Nov 2007 01:33 AM by tom27. 3 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
New Member
New Member

17 Jan 2006 12:11 PM
Helping a developer with an sp from a .net program which will feed it several parameters (any of which can be null). In other words the where clause is somewhat dynamic. I am posting the select statement (not the parameters) he used. Currently this sp runs very slow. I was considering building some type of dynamic where clause to check for the parameters to create that, but was afraid that in itself might be just as bad. Any better way to handle it?

FROM vGrievances
coalesce(GrievanceID,-1) = coalesce( @GID, GrievanceID, -1)
AND coalesce(Member, 'x') = coalesce( @M , Member, 'x')
AND coalesce(Subject,'x') LIKE coalesce( '%' + @S + '%' , Subject,'x')
AND coalesce(GrievDesc,'x') LIKE coalesce('%' + @GD+ '%', GrievDesc,'x')
AND coalesce(OwnerID, -1) = coalesce( @OID, OwnerID, -1)
AND coalesce(CreatorID, -1) = coalesce( @CRID, CreatorID, -1)
AND coalesce(AssignedToID, -1) = coalesce( @ATID, AssignedToID, -1)
AND DueDate >= coalesce(@DDS, DueDate)
AND DueDate <=coalesce(@DDE, DueDate)
AND coalesce(ContractID, -1) = coalesce(@COID, ContractID, -1)
AND coalesce(GrievTypeID, -1) = coalesce(@GTID , GrievTypeID, -1)
AND coalesce(StatusName,'x') = coalesce(@SN, StatusName,'x')
AND CreatedDate >= coalesce(@CDS, CreatedDate)
AND CreatedDate <= coalesce(@CDE, CreatedDate)
AND coalesce(StatusChangedDate,'1/1/1900') >= coalesce(@SCDS , StatusChangedDate,'1/1/1900')
AND coalesce(StatusChangedDate,'1/1/1900') <= coalesce(@SCDE, StatusChangedDate,'1/1/1900')
AND coalesce(Division,'x') = coalesce(@D, Division,'x')
AND coalesce(PriorityTypeID, -1) = coalesce(@PTID, PriorityTypeID, -1)
AND coalesce(OutcomeID, -1) = coalesce(@OcID, OutcomeID, -1)
AND ContractID IN (
AND GrievanceID between 1 AND 500

New Member
New Member

17 Jan 2006 03:36 PM
Applying functions to the field names will preclude the query from ever using an index. I would rewite each of the WHERE clause statements as...

WHERE (@GID is null or GrievanceID = @GID)
and (@M is null or Member = @M)

New Member
New Member

02 Nov 2007 01:51 AM
I saw this 'old' topic. I have the same problem. My SQL. lngbcid has an index. But this statement doesn't use the index.

Without 'or @ind is null' the index is used.

Doese anyone has an idea?


declare @int int
set @int = 1

select * from dbo.tabBC
WHERE (lngbcid = @int or @int is null )
New Member
New Member

06 Nov 2007 01:33 AM
I prefer a parametric dynamic sql here. But some times I felt not using the optimal plan even if I update statistics, rebuild indexes etc.
You are not authorized to post a reply.

Acceptable Use Policy