Tuesday, January 31, 2012

Dynamic SQL with multi-valued Where clause

WOW.  This is going to be a good one to explain.  Let’s take a concept of we have a customer table that has items like: First Name, Last Name, Member Since Date, State (as in which state they live in in the US), and total purchases (float).
So if we wanted to get every record in the table our Stored Procedure (we’ll refer to this as SP throughout this article) might look like this:
Select



[first_name]



,    [last_name]



,    [member_since_date]



,    [state]



,    [total_purchases]



From dbo.Customers




Simple and plain enough. Notice my brackets around the field names.  In short, this tells SQL that whatever is in the brackets is a name of an object (as opposed to a keyword or such) thus, we can have a field name of STATE and SQL will not confuse it for a keyword.

Now we need to change this into a Dynamically created SQL statement and we will need to do this in a SP.  So Create a New Stored Procedure in your database.  We’ll look at the code and then I will explain line by line (as best I can).




Use [Customers]



SET ANSI_NULLS ON



GO



SET QUOTED_IDENTIFIER ON



GO



 



CREATE PROCEDURE dbo.getCustomerDataUsingDynamicSQL 



-- Add the parameters for the stored procedure here



@state varchar(2) = ''



,    @firstname varchar(50) = ''



,    @lastname varchar(50) = ''






AS



BEGIN



-- SET NOCOUNT ON added to prevent extra result sets from



-- interfering with SELECT statements.



SET NOCOUNT ON;



Declare 



@sql_statement    nvarchar(max)



,    @sql_0            nvarchar(max)



,    @sql_1            nvarchar(max)



,    @sql_2            nvarchar(max)



,    @sql_3            nvarchar(max)






-- Insert statements for procedure here



SELECT @sql_0 =



'    Select



[first_name]



,    [last_name]



,    [member_since_date]



,    [state]



,    [total_purchases]



From dbo.Customers



Where 1=1 '



 



IF @state <> ''



BEGIN



SELECT



@sql_1 = ' AND [state] = ''' + @state + ''' '






END



ELSE



BEGIN



SELECT



@sql_1 = ''



END



 



IF @firstname <> ''



BEGIN



SELECT



@sql_2 = ' AND [first_name] = ''' + @firstname + ''' '






END



ELSE



BEGIN



SELECT



@sql_2 = ''



END



 



IF @lastname <> ''



BEGIN



SELECT



@sql_3 = ' AND [last_name] = ''' + @last_name + ''' '






END



ELSE



BEGIN



SELECT



@sql_3 = ''



END



SELECT 



@sql_statement = @sql_0 + @sql_1 + @sql_2 + @sql_3



 



-- SELECT @sql_statement






EXECUTE sp_executesql @statement = @sql_statement



 



-- dbo.getCustomerDataUsingDynamicSQL



-- dbo.getCustomerDataUsingDynamicSQL @state = 'GA'



-- dbo.getCustomerDataUsingDynamicSQL @firstname = 'John'



-- dbo.getCustomerDataUsingDynamicSQL @lastname = 'Smith'



END



GO




So that is our entire SP.

Starting at line 1 – We’re telling the server which database we intend to use with the SP.


After our create statement, we setup our input parameters.  We need an input parameter for each segment of our where clause.   We also instantiate the variable with a blank value.  This is so that if we don’t pass a value, it will not gives us an error and we can leave it out of the where clause.

We also need to DECLARE local variables to build our SQL Statement.


The @sql_statement variable we will use to concatenate all the other variables into one, this is not necessary but I do it for readability and also debugging (explained below).


Now notice that in our Select statement we are setting our @sql_0 to our basic SELECT statement in a text string.  We convert the SELECT statement into a string by enclosing it in single quotes.


So after our SELECT statement we come to our WHERE clause.  The reason we start our WHERE clause with 1=1 is that we have to establish a where clause for our dynamic SQL and higher mathematics (addition) tells us that it will always render TRUE.


The IF ELSE statement has two parts.  If the variable is not blank then we will add our AND to the WHERE clause.  Thus, it would end up like – Where 1=1 AND [first_name] = ‘John’.


This brings us to where we SELECT our concatenated statement into one variable.  At this point, our entire select statement rests in the one variable. 
However, if you comment out this line: @sql_statement = @sql_0 + @sql_1 + @sql_2 + @sql_3


And uncomment this line: -- SELECT @sql_statement


Your result set will be your SQL statement.  That means you can use it to debug your SQL statement because this is what is actually sent to SQL for processing (You can see how your code is built and where your error is).


This line is the one that actually runs your code: EXECUTE sp_executesql @statement = @sql_statement


I always comment out the SP name with all the variables so all I have to do is highlight the SP name and variable and click execute and quickly execute the code for debugging purposes.


That’s basically how you build dynamic SQL.


Happy .Netting – Saleh…
UPDATE: So obviously I've had one too many Rock Ons.  You want to comment out:
EXECUTE sp_executesql @statement = @sql_statement
And not:
-- Select @sql_statement + @sql_0... for debugging purposes.  I thought that I had better correct the minor typo before I started getting hate mail.

Hack The Gibson.

2 comments:

  1. Cool. So assuming I wanted to specify multiple parameters (say, @lastname & @state) would I separate by comma?

    ReplyDelete
  2. thats correct i.e.
    -- dbo.spname @variable1 = 'test', @variable2 = 'test2'

    ReplyDelete