Friday, April 10, 2009

Building Efficient & Dynamic Query in SQl server.

Parameterized Dynamic Sql Query.
We often need to create dynamic queries inside Stored procedure in sql server.
for that we concatenate DML statements and input parameter of stored procedure and a string of statements. And than Use Exec statement to execute those statements..

CREATE Procedure GetUsers
@ID int =0,
@UserID int=0
declare @Query nvarchar(500)
set @Query='select * from Your_TableName where ID>'+cast(@ID as varchar(100))+' and UserID >'+cast(@UserID as varchar(100))
exec (@Query)
END --exec GetUsers 14,45

This is not good practice for some reasons.
(1)it leads possibility of sql injection.
(2)it is not efficient because it is not precompiled.
(3)each time it will consume new memory because it’s caching will not managed.

I have anothere approach to do that that is Parameterized Dynamic sql query.
It almost take same time to create and resolve issue mentiones. To do that we just need to .

(1) Declare a string of sql statements.
(2) string of declaration of parameter you want to pass in this string.

CREATE Procedure GetUsers
@ID int =0,
@UserID int=0

declare @Query nvarchar(500) --Statements to execure
declare @Parameters nvarchar(500) --Parameters to pass in
set @Query='select * from NHA_mstBuilders where ID>@QID and UserID>@QUserID'
set @Parameters='@QID int,@QUserID int'--parameter declaration
exec sp_executesql @Query,@Parameters,@QID=@ID,@QUserID=@UserID --pass statements,then parameters and then assing values to that parameters.
END --exec GetUsers 14,45

Than use sp_exectesql to execute dynamic query..

No comments:

Post a Comment

Comments posted on ASP.Net Ajax Tutorials Blog are moderated and will be approved only if they are on-topic and not abusive. Please email me or my team for tech-support or blogging related questions. Avoid including website URLs in your comments - Thanks Author