Avoid Dynamic Queries

 

A sample dynamic query goes like this,

CREATE PROCEDURE sp_Search_Employee
(
@iEmployeeId AS  INT = 0,
@sEmployeeName AS VARCHAR(50)=''
)
AS
BEGIN

DECLARE @sSQLSearch AS VARCHAR(1000)
SET @sSQLSearch = ' SELECT *  FROM tbl_Employee '
SET @sSQLSearch = @sSQLSearch + ' WHERE 1 = 1  AND ' 

IF @iEmployeeId <> 0
BEGIN
SET  @sSQLSearch = @sSQLSearch + ' EmployeeID = ' + CAST(@iEmployeeId AS  VARCHAR)
END

IF @sEmployeeName <> 0
BEGIN
SET @sSQLSearch = @sSQLSearch + ' EmployeeName = ' + @sEmployeeName
END

EXEC(@sSQLSearch)

END

Instead of the above Query, reforming the query without using Dynamic SQL can be done like this:

CREATE PROCEDURE sp_Search_Employee
(
@iEmployeeId  AS INT = 0,
@sEmployeeName AS VARCHAR(50)=''
)
AS
BEGIN

SELECT * FROM tbl_Employee
WHERE
(
@iEmployeeId = 0  OR
EmployeeId = @iEmployeeId
) 
AND
(
@sEmployeeName = '' OR
EmployeeName = @sEmployeeName
)

END

 ---------------------------------------------------------------------------------------

Dynamic SQL can be created easily but it comes along with a few drawbacks:

 
1. The main advantage of using Stored Procedures in SQL is Precompiliation. All the SPs are stored in a precompiled form and hence they are faster in execution. On the contrary, the main SELECT query in a dynamic SQL is NOT Precompiled. This hampers the performance.
2. The maximum length EXEC can handle is 8000 (if VARCHAR is used to build the query). Queries going beyond the limit can not be executed.

 

Wise Computing!!

Save Server Power, Save Energy, Save Earth