Use of Dynamic SqlQuery and its Optimization

Before writing this article I have many misconceptions for using the dynamic sql in sqlserver.
We are basically using the Execute statement for executing the dynamic query in sqlserver.
There are two options to execute the dynamic query
1) Execute @SqlQuery
2) Execute Sp_executesql @Sqlquery
In this article I will explore the Second option and how useful that option in Development.
Below is the example of Sp_executesql.

DECLARE @IntPatientID int;

DECLARE @SQLString nvarchar(500);

DECLARE @ParmDefinition nvarchar(500);

DECLARE @EncouterCount int;

SET @IntPatientID = 286;
SET @SQLString = N’Select @EncouterCnt=count(EncounterID) from Pat_Encounter where PatientID = @PatientID’;
SET @ParmDefinition = N’@PatientID int, @EncouterCnt int OUTPUT’;

EXECUTE sp_executesql @SQLString, @ParmDefinition, @PatientID = @IntPatientID, @EncouterCnt=@EncouterCount OUTPUT;

SELECT @EncouterCount;

In Execute option you will not get option of parameter you must define the variable in the dynamic sql to achieve the functionality.
In Sp_executesql you will get input and output parameter and that’s very useful in Development.
Another thing is Execute is not reuse Execution query plan else you are not define same value in condition of where.
Sp_executesql is reuse the Execution Plan and give fast output then Execute clause.
For the Optimization Sp_executesql is very handy in procedure.
You will get syntax from below link.

I hope this article is useful to you and enhance your knowledge of sqlserver.


One thought on “Use of Dynamic SqlQuery and its Optimization

  1. Pingback: Sqlserver Query optimization Tips « Kuldip Bhatt's Blog About SQL

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s