How to create a stored procedure with dynamic SQL query

Problem: In some cases, we like to use a dynamically built sql queries based on the parameter selected.

Solution:

sp_executesql, starting from SQL server 2008 or higher.

This is a Database Engine Stored procedure under the stored procedures, which are generally used for general maintenance on the instance.

Executes a Transact-SQL statement or batch that can be reused many times, or one that has been built dynamically. The Transact-SQL statement or batch can contain embedded parameters.

In our daily usage we can use this procedure to build the necessary query to fit your need.

Example:

Let’s explore a good use of this dynamic script execution is search statements and order by columns. I have a table called Employees where I would like to select all the employees and filter with optional criteria and also sort with optional criteria. So I use two variables to build the conditions for where and order by and then concatenate to get the entire SQL statement. We can fiddle this query to get as complicated as we want based on the variables and selection criteria of parameters.

Below is the script to create a procedure to search and order by dynamically:

CREATE PROCEDURE [dbo].[SearchEmployees] @Name VARCHAR(32) = NULL

                ,@EmployeeType VARCHAR(32) = NULL

                ,@Sortby VARCHAR(50) = NULL

                ,@SortOrder BIT = NULL

                WITH EXECUTE AS CALLER

AS

BEGIN

                DECLARE @ParmDefinition NVARCHAR(2000)

                                ,@strSQL NVARCHAR(4000)

                                ,@Where NVARCHAR(4000)

                                ,@Orderby NVARCHAR(100)

                                ,@Sorting VARCHAR(10)

                SELECT @ParmDefinition = N'

                @Name VARCHAR(32) = NULL

                ,@EmployeeType VARCHAR(32) =NULL

                ,@Sortby VARCHAR(50) = NULL

                ,@SortOrder BIT = NULL'

                SET NOCOUNT ON;

                SET @StrSQL = ''

                SET @Where = 'Where'

                IF @Name IS NOT NULL

                                SET @Where = @Where + '          Name=@Name AND'

                IF @EmployeeType IS NOT NULL

                                SET @Where = @Where + '          EmployeeType=@EmployeeType AND'

                IF @Where = 'WHERE' -- no parameters were passed in 

                                SET @Where = ''

                ELSE -- parameters were passed, trim off the last AND 

                                SET @Where = LEFT(@Where, len(@Where) - 4)

                -- Sorting order default ascending order if 1 is passed descending order.

                IF (@SortOrder = '1')

                                SET @Sorting = ' desc'

                ELSE

                                SET @Sorting = ' asc'

                -- Sort by column name and sorting order is consructed dynamically.

                SET @Orderby = ''

                -- Code for Sorting the  Columns

                IF @sortby IS NOT NULL

                                AND @sortby = 'Name'

                                SET @Orderby = 'ORDER BY name' + @sorting

                IF @sortby IS NOT NULL

                                AND @sortby = 'EmployeeType'

                                SET @Orderby = 'ORDER BY EmployeeType' + @sorting

                IF @Orderby = '' -- no parameters were passed in 

                                SET @Orderby = ''

                SET @StrSQL = 'SELECT [EmployeeID],[Name],[PhoneNumber],[EmployeeType] FROM [dbo].[Employees]  ' + @Where+' ' + @orderby

                EXEC sp_EXECUTESQL @strSQL

                                ,@ParmDefinition

                                ,@Name = @Name

                                ,@EmployeeType = @EmployeeType

                                ,@Sortby = @Sortby

                                ,@SortOrder = @SortOrder

END

Execution Results: