Consider the scenario of providing Search functionality to the users. You may be creating forms that display couple of text boxes and a grid and allow the users to search rows based on the values entered in the text boxes and the rows if found, will be populated in Grid. For example, you may create a Search form for searching employees based on First Name, Last Name or Department. User may enter values in either one of the text boxes or values in all the three text boxes or any combination of values.
Also, you may need to develop a stored procedure that takes FirstName, LastName and Department as the parameters and return the Employees record set based on the parameters and display it in the grid.
Generally developers tend to use dynamic query to perform this search as in the following procedure:
CREATE PROCEDURE uspEmployeeSearch1
@FirstName VARCHAR(50) = NULL,
@LastName VARCHAR(50) = NULL,
@DepartmentName VARCHAR(50) = NULL
AS
BEGIN
DECLARE @Sql VARCHAR(4000)
SET @Sql = ' SELECT tblEmployee.EmpID, tblEmployee.FirstName, '
SET @Sql = @Sql + 'tblEmployee.LastName, tblEmployee.EmpDOB, '
SET @Sql = @Sql + ' tblDepartment.DeptID, tblDepartment.DeptName '
SET @Sql = @Sql + ' FROM tblEmployee '
SET @Sql = @Sql + ' INNER JOIN tblDepartment '
SET @Sql = @Sql + ' ON tblEmployee.DeptID = tblDepartment.DeptID '
SET @Sql = @Sql + ' WHERE 1 = 1 '
IF @FirstName IS NOT NULL
BEGIN
SET @Sql = @Sql + ' AND tblEmployee.FirstName LIKE ''' + '%'
SET @Sql = @Sql + @FirstName + '%'''
END
IF @LastName IS NOT NULL
BEGIN
SET @Sql = @Sql + ' AND tblEmployee.LastName LIKE ''' + '%'
SET @Sql = @Sql + @LastName + '%'''
END
IF @DepartmentName IS NOT NULL
BEGIN
SET @Sql = @Sql + ' AND tblDepartment.DeptName LIKE ''' + '%'
SET @Sql = @Sql + @DepartmentName + '%'''
END
EXEC (@Sql)
END
There are few concerns with the above procedure. Since the stored procedure uses the dynamic query, SQL Server parses the query each time when it is executed. This adds an extra time to the execution of the procedure. Also developers may do mistakes in the syntax of the query which will not be detected during compilation or until running the procedure for all the combinations of parameters. Also the procedure is prone to SQL injection attacks.
The following version of stored procedure that does not use dynamic query solves all the above issues:
CREATE PROCEDURE uspEmployeeSearch2
@FirstName VARCHAR(50) = NULL,
@LastName VARCHAR(50) = NULL,
@DepartmentName VARCHAR(50) = NULL
AS
BEGIN
SELECT tblEmployee.EmpID,
tblEmployee.FirstName,
tblEmployee.LastName,
tblEmployee.EmpDOB,
tblDepartment.DeptID,
tblDepartment.DeptName
FROM
tblEmployee
INNER JOIN tblDepartment
ON tblEmployee.DeptID = tblDepartment.DeptID
WHERE
( tblEmployee.FirstName LIKE '%' + @FirstName + '%'
OR @FirstName IS NULL)
AND ( tblEmployee.LastName LIKE '%' + @LastName + '%'
OR @LastName IS NULL)
AND ( tblDepartment.DeptName LIKE '%' + @DepartmentName + '%'
OR @DepartmentName IS NULL)
END
Although the second version appears to be simple change over the first one, it is very efficient.
|
No responses found. Be the first to respond and make money from revenue sharing program.
|