Threaded Mode | Linear Mode

**vijayvishwakarma** · 03-29-2012, 04:23 PM

In many scenarios you will need to pass a set of parameters to get the output in your project. Some of these parameters will be compulsory and some may not. In such cases you can you a dynamic where clause based T-SQL to get the output. Below is very simple example of one such case.

Consider a case where you have 4 search parameters. You can pass all 4 to get the result set or no parameters to get all saved in database.

Code:

Create Procedure DynamicSQL

    @param1 int,

    @param2 int,

    @param3 int,

    @param4 int

AS

    SET NOCOUNT ON

    Declare @SQLQuery AS NVarchar(max)

    Declare @ParmDefinition AS NVarchar(2000) 

    SET @SQLQuery = 'Select * From tablename where (1=1) ' /* Default sql to get all the records from database*/ 

    If @param1 Is Not Null 

         Set @SQLQuery = @SQLQuery + ' And (columnName = @param1)'

    If @param2 Is Not Null 

         Set @SQLQuery = @SQLQuery + ' And (columnName = @param2)'

   If @param3 Is Not Null 

         Set @SQLQuery = @SQLQuery + ' And (columnName = @param3)'

    If @param4 Is Not Null 

         Set @SQLQuery = @SQLQuery + ' And (columnName = @param4)'

    /* Specify Parameter Format for all input parameters included 

     in the statementt */

    Set @ParmDefinition =      ' @param1 int,

                                         @param2 int,

                                         @param3 int,

                                         @param4 int'

    /* Execute the Transact-SQL String with all parameter value's 

       Using sp_executesql Command */

    Execute sp_Executesql     @SQLQuery, 

                @ParmDefinition, 

                @param1, 

                @param2, 

                @param3, 

                @param4

    If @@ERROR <> 0 GoTo ErrorHandler

    SET NOCOUNT OFF

    Return(0)

ErrorHandler:

    Return(@@ERROR)

GO

The above query will be build on the fly based on the input parameters passed and executed against the database to get the output.THe variable @SQLQuery is used to build the dynamic SQL-statement and @ParmDefinition is used to define the Parameter's format.

In the sp we are checking if the input parameter is null, we are not including that in our query. I am checking for null only, you can have your own methods or values to be evaluated to create the query.

sp_Executesql is used to execute the sql to get the output.

Pros : Fast execution, flexibility
Cons : Cannot be checked for the accuracy of the sql as with other stored procedure so one has to be very careful while using this kind of T-SQL.

Possibly Related Threads...
Thread:		Author	Replies:	Views:	Last Post
	SQL SERVER – Executing Dynamic SQL – SQL Queries 2012 Joes 2 Pros Volume 4 – Query Programming Objects for SQ	Pinal Dave	0	103	03-28-2013 07:48 AM Last Post: Pinal Dave
	SQL SERVER – Avoid Using Function in WHERE Clause – Scan to Seek	Pinal Dave	0	88	03-12-2013 08:31 AM Last Post: Pinal Dave