April 27, 2009 at 6:22 am
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
April 27, 2009 at 6:29 am
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
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
April 27, 2009 at 7:17 am
Thanks.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply