Alternative way to write dynamic SQL queries


Introduction

When we write Dynamic queries, most of the time only “WHERE” clause needs to be dynamic in the stored procedure rest of the part remains static, I mean “SELECT” and “FROM” Clause remain static most of the time.

To write dynamic query, we are choosing variables so that we can write “WHERE” clause using appropriate conditions and then putting a query in variables. Writing queries like this generates so much confusion while debugging and maintenance of the query.

Here is one of the process by which we can eliminate use of variable while writing queries which provide the same result as a dynamically written query provides.

We can use this technique if the “SELECT” list is going to be static and we need to take care of only “WHERE” clause. Otherwise we can use variables to store SQL query if the columns in the “SELECT” list will be generated dynamically and/or name of the source table will be decided at runtime. To understand this approach let us see one example.

Example

Original Stored Procedure:

We have one stored procedure as below

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

— exec [SC_Report_GetDataByCustomerLounge] 0,1,0,’2009-01-01′,’2009-03-01′

— exec [SC_Report_GetDataByCustomerLounge] 0,0,0,”,”

ALTER PROCEDURE [dbo].[SC_Report_GetDataByCustomerLounge]

@CustomerId int = 0,

@LoungeId int = 0,

@CountryId int = 0,

@FromDate datetime,

@ToDate datetime

AS

DECLARE @StrQuery nvarchar(3000)

DECLARE @WhereCondition nvarchar(3000)

SET @WhereCondition =

BEGIN

IF(@CustomerId != 0)

BEGIN

SET @WhereCondition = @WhereCondition + ‘ Trans.CustomerId = ‘ + Convert(varchar,@CustomerId)

END

IF(@LoungeId != 0 AND @WhereCondition != )

BEGIN

SET @WhereCondition = @WhereCondition + ‘ AND Trans.LoungeId = ‘ + Convert(varchar,@LoungeId)

END

ELSE IF(@LoungeId != 0)

BEGIN

SET @WhereCondition = @WhereCondition + ‘ Trans.LoungeId = ‘ + Convert(varchar,@LoungeId)

END

IF(@CountryId != 0 AND @WhereCondition != )

BEGIN

SET @WhereCondition = @WhereCondition + ‘ AND Trans.CountryId = ‘ + Convert(varchar,@CountryId)

END

ELSE IF(@CountryId != 0)

BEGIN

SET @WhereCondition = @WhereCondition + ‘ Trans.CountryId = ‘ + Convert(varchar,@CountryId)

END

IF (@FromDate != ’01/01/1900′ AND @ToDate != ’01/01/1900′ AND @WhereCondition != )

BEGIN

SET @WhereCondition = @WhereCondition + ‘ AND Convert(varchar(10),Trans.AccessDate,101) >= ”’ +

Convert(varchar,Convert(varchar(10),@FromDate,101)) + ”’ AND

Convert(varchar(10),Trans.AccessDate,101) <= ”’ +

Convert(varchar,Convert(varchar(10),@ToDate,101)) + ””

END

ELSE IF (@FromDate != ’01/01/1900′ AND @ToDate != ’01/01/1900′)

BEGIN

SET @WhereCondition = @WhereCondition + ‘ Convert(varchar(10),Trans.AccessDate,101) >= ”’ +

Convert(varchar,Convert(varchar(10),@FromDate,101)) + ”’ AND Convert(varchar(10),

Trans.AccessDate,101) <= ”’ + Convert(varchar,Convert(varchar(10),@ToDate,101)) + ””

END

IF(@WhereCondition != )

BEGIN

Set @StrQuery = ‘Select Trans.TransactionId,Lounge.LoungeId,Cust.CustomerId,

(Cust.FirstName + ” ” + Cust.MiddleName + ” ”+ Cust.LastName ) As CustomerName,

Lounge.LoungeName,City.CityName,Country.CountryName,Cust.Address1,Cust.Address2,

Cust.MobileNo,Cust.EmailAdress,Airport.AirportName,Trans.AccessDate,Trans.Remarks

from dbo.CustomerInformation as Cust

Inner join dbo.TransactionMaster Trans ON Cust.CustomerId = Trans.CustomerId

Inner join dbo.LoungeInformation as Lounge ON Lounge.LoungeId = Trans.LoungeId

Inner join dbo.CityInformation as City ON City.CityId = Trans.CityId

Inner join dbo.CountryInformation as Country ON Country.CountryId = Trans.CountryId

Inner join dbo.AirportInformation as Airport ON Airport.AirportId = Trans.AirportId

WHERE ‘ + @WhereCondition

END

ELSE

BEGIN

Set @StrQuery = ‘Select Trans.TransactionId,Lounge.LoungeId,Cust.CustomerId,

(Cust.FirstName + ” ” + Cust.MiddleName + ” ”+ Cust.LastName ) As CustomerName,

Lounge.LoungeName,City.CityName,Country.CountryName,Cust.Address1,Cust.Address2,

Cust.MobileNo,Cust.EmailAdress,Airport.AirportName,Trans.AccessDate,Trans.Remarks

from dbo.CustomerInformation as Cust

Inner join dbo.TransactionMaster Trans ON Cust.CustomerId = Trans.CustomerId

Inner join dbo.LoungeInformation as Lounge ON Lounge.LoungeId = Trans.LoungeId

Inner join dbo.CityInformation as City ON City.CityId = Trans.CityId

Inner join dbo.CountryInformation as Country ON Country.CountryId = Trans.CountryId

Inner join dbo.AirportInformation as Airport ON Airport.AirportId = Trans.AirportId ‘

END

PRINT @StrQuery

exec sp_executesql @StrQuery

END

From above stored procedure we can see that SELECT query part is static and the query is building dynamically as per change in the WHERE Clause. The above query gives me the following output.

Result before applying the approach

Result before applying the approach

Alternative way to write same query:

We will be able to replace the above stored procedure using the following stored procedure and that will give same result as previous one.

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

— exec [SC_Report_GetDataByCustomerLounge] 0,1,0,’2009-01-01′,’2009-03-01′

— exec [SC_Report_GetDataByCustomerLounge] 0,0,0,”,”

ALTER PROCEDURE [dbo].[Test]

@CustomerId int = NULL,

@LoungeId int = NULL,

@CountryId int = NULL,

@FromDate datetime = NULL,

@ToDate datetime = NULL

AS

DECLARE @StrQuery nvarchar(3000)

DECLARE @WhereCondition nvarchar(3000)

SET @WhereCondition =

BEGIN

Select Trans.TransactionId,Lounge.LoungeId,Cust.CustomerId,

(Cust.FirstName + ‘ ‘ + Cust.MiddleName + ‘ ‘+ Cust.LastName ) As CustomerName,

Lounge.LoungeName,City.CityName,Country.CountryName,Cust.Address1,Cust.Address2,

Cust.MobileNo,Cust.EmailAdress,Airport.AirportName,Trans.AccessDate,Trans.Remarks

from dbo.CustomerInformation as Cust

Inner join dbo.TransactionMaster Trans ON Cust.CustomerId = Trans.CustomerId

Inner join dbo.LoungeInformation as Lounge ON Lounge.LoungeId = Trans.LoungeId

Inner join dbo.CityInformation as City ON City.CityId = Trans.CityId

Inner join dbo.CountryInformation as Country ON Country.CountryId = Trans.CountryId

Inner join dbo.AirportInformation as Airport ON Airport.AirportId = Trans.AirportId

WHERE (Trans.CustomerId = Convert(varchar,@CustomerId) OR Trans.CustomerId = NULL)

AND (Trans.LoungeId = Convert(varchar,@LoungeId) OR Trans.LoungeId = NULL)

AND (Trans.CountryId = Convert(varchar,@CountryId) OR Trans.CountryId = NULL)

AND (Convert(varchar(10),Trans.AccessDate,101) >= Convert(varchar,Convert(varchar(10),@FromDate,101))

AND Convert(varchar(10),Trans.AccessDate,101) <= Convert(varchar,Convert(varchar(10),@ToDate,101)))

END

The above stored procedure gives me the following output:

Result after applying the approach

Result after applying the approach

The benefits of using this technique are:

  • It will reduce the complexity of query.

  • Using this, we can easily maintain the query.

  • Syntax checking will be much simpler as most of the time quote(‘) gives problem to novice developers when query is stored in a variable.

  • And most important, when we use LINQ –to-SQL in our application structure it will reduce the conversion error between ISingleResult and INT in the case of writing dynamic query.

References:

http://www.SQLServerCentral.com

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: