Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Avoiding Dynamic Queries Expand / Collapse
Author
Message
Posted Monday, April 27, 2009 6:22 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 1, 2013 2:51 AM
Points: 283, Visits: 312
We have an Web application using sql server. In most of the sps, generally we have a parameter @strcriteria for the criteria part (where clause) of the query. The criteria will have value like 'where @clientId = 1 and ProgramId = 90 and projectid = 66;' or it can be empty also. Due to this criteria part most of the queries are made into dynamic queries. Is there any option/method to avoid dynamic query in this case?

Eg.
CREATE PROCEDURE dbo.usp_Rpt1 @strCriteria VARCHAR(500)
AS
BEGIN

SET NOCOUNT ON

DECLARE @strQuery VARCHAR(8000)

SET @strQuery = ' SELECT' +
' c_Client.ClientID,' +
' ClientName FROM Client '
ISNULL(@strCriteria,'')

EXEC (@strQuery)

SET NOCOUNT Off
END


Rgds
Mohan Kumar
Post #704857
Posted Monday, April 27, 2009 6:29 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, February 24, 2010 4:10 AM
Points: 1,550, Visits: 2,232
Here is an article that Gail touches on about this topic.

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/


----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley


Posting Best Practices
Numbers / Tally Tables

SQL-4-Life
Post #704865
Posted Monday, April 27, 2009 7:17 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 1, 2013 2:51 AM
Points: 283, Visits: 312
Thanks.
Post #704905
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse