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.
http://msdn.microsoft.com/en-us/library/ms188001(SQL.90).aspx

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