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
as BEGIN
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
as BEGIN

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..