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

A Script to Find Latest Database backup


USE MASTER

GO

IF OBJECT_ID(‘USP_DBA_RECENTBACKUPS’) IS NOT NULL

DROP PROC USP_DBA_RECENTBACKUPS

GO

exec USP_DBA_RECENTBACKUPS ‘NTERP’,‘R’

CREATE PROC USP_DBA_RECENTBACKUPS

@P_DBNAME VARCHAR(200)= NULL,

@PLAN CHAR(1)= NULL

AS

BEGIN

DECLARE @DB_NAME VARCHAR(200)

SET @DB_NAME=@P_DBNAME

IF @DB_NAME IS NULL

BEGIN

WITH

BACKUP_RECENT AS

(

SELECT MAX(BACKUP_DATE) BACKUP_DATE,MAX(ID) ID,[TYPE],SERVER_NAME,DATABASE_NAME

FROM

(

SELECT ROW_NUMBER() OVER (ORDER BY DATABASE_NAME,[TYPE],BACKUP_FINISH_DATE) ID,

BACKUP_FINISH_DATE BACKUP_DATE, PHYSICAL_DEVICE_NAME,A.MEDIA_SET_ID,

SERVER_NAME,DATABASE_NAME,[TYPE]

FROM MSDB.DBO.BACKUPSET A JOIN MSDB.DBO.BACKUPMEDIAFAMILY B

ON(A.MEDIA_SET_ID=B.MEDIA_SET_ID)

) BACKUPS

GROUP BY [TYPE],SERVER_NAME,DATABASE_NAME

),

BACKUP_ALL AS

(

SELECT ROW_NUMBER() OVER (ORDER BY DATABASE_NAME,[TYPE],BACKUP_FINISH_DATE) ID,

PHYSICAL_DEVICE_NAME

FROM MSDB.DBO.BACKUPSET A JOIN MSDB.DBO.BACKUPMEDIAFAMILY B

ON(A.MEDIA_SET_ID=B.MEDIA_SET_ID)

)

SELECT SERVER_NAME [SERVER],DATABASE_NAME [DATABASE],BAKUPTYPE=

CASE WHEN [TYPE]=‘D’ THEN ‘FULL’

WHEN [TYPE]=‘I’ THEN ‘DIFFERENTIAL’

WHEN [TYPE]=‘L’ THEN ‘LOG’

WHEN [TYPE]=‘F’ THEN ‘FILE / FILEGROUP’

WHEN [TYPE]=‘G’ THEN ‘DIFFERENTIAL FILE’

WHEN [TYPE]=‘P’ THEN ‘PARTIAL’

WHEN [TYPE]=‘Q’ THEN ‘DIFFERENTIAL PARTIAL’

END,BACKUP_DATE [RECENT BACKUP], PHYSICAL_DEVICE_NAME [LOCATION] FROM BACKUP_RECENT,BACKUP_ALL

WHERE BACKUP_RECENT.ID=BACKUP_ALL.ID

ORDER BY SERVER_NAME,DATABASE_NAME,BACKUP_DATE

END

ELSE

BEGIN

IF @PLAN = ‘R’

BEGIN

WITH

BACKUP_RECENT AS

(

SELECT MAX(BACKUP_DATE) BACKUP_DATE,MAX(ID) ID,[TYPE],SERVER_NAME,DATABASE_NAME

FROM

(

SELECT ROW_NUMBER() OVER (ORDER BY DATABASE_NAME,[TYPE],BACKUP_FINISH_DATE) ID,

BACKUP_FINISH_DATE BACKUP_DATE, PHYSICAL_DEVICE_NAME,A.MEDIA_SET_ID,

SERVER_NAME,DATABASE_NAME,[TYPE]

FROM MSDB.DBO.BACKUPSET A JOIN MSDB.DBO.BACKUPMEDIAFAMILY B

ON(A.MEDIA_SET_ID=B.MEDIA_SET_ID)

) BACKUPS

GROUP BY [TYPE],SERVER_NAME,DATABASE_NAME

),

BACKUP_ALL AS

(

SELECT ROW_NUMBER() OVER (ORDER BY DATABASE_NAME,[TYPE],BACKUP_FINISH_DATE) ID,

PHYSICAL_DEVICE_NAME

FROM MSDB.DBO.BACKUPSET A JOIN MSDB.DBO.BACKUPMEDIAFAMILY B

ON(A.MEDIA_SET_ID=B.MEDIA_SET_ID)

)

SELECT * FROM(

SELECT SERVER_NAME [SERVER],DATABASE_NAME [DATABASE],BAKUPTYPE=

CASE WHEN [TYPE]=‘D’ THEN ‘FULL’

WHEN [TYPE]=‘I’ THEN ‘DIFFERENTIAL’

WHEN [TYPE]=‘L’ THEN ‘LOG’

WHEN [TYPE]=‘F’ THEN ‘FILE / FILEGROUP’

WHEN [TYPE]=‘G’ THEN ‘DIFFERENTIAL FILE’

WHEN [TYPE]=‘P’ THEN ‘PARTIAL’

WHEN [TYPE]=‘Q’ THEN ‘DIFFERENTIAL PARTIAL’

END,BACKUP_DATE [RECENT BACKUP], PHYSICAL_DEVICE_NAME [LOCATION] FROM BACKUP_RECENT,BACKUP_ALL

WHERE BACKUP_RECENT.ID=BACKUP_ALL.ID AND DATABASE_NAME=@P_DBNAME AND [TYPE] IN (‘D’,‘I’)

UNION

SELECT

SERVER_NAME,DATABASE_NAME,BAKUPTYPE=‘LOG’,

BACKUP_FINISH_DATE BACKUP_DATE,PHYSICAL_DEVICE_NAME [LOCATION]

FROM MSDB.DBO.BACKUPSET A JOIN MSDB.DBO.BACKUPMEDIAFAMILY B

ON(A.MEDIA_SET_ID=B.MEDIA_SET_ID)

WHERE [TYPE]=‘L’

AND BACKUP_FINISH_DATE>

(

SELECT TOP 1 BACKUP_FINISH_DATE FROM MSDB.DBO.BACKUPSET WHERE [TYPE] IN (‘D’,‘I’)

AND DATABASE_NAME= @DB_NAME ORDER BY BACKUP_FINISH_DATE DESC

)

AND DATABASE_NAME= @DB_NAME ) AS RESTORE_PLAN

ORDER BY [RECENT BACKUP]

END

ELSE

BEGIN

WITH

BACKUP_RECENT AS

(

SELECT MAX(BACKUP_DATE) BACKUP_DATE,MAX(ID) ID,[TYPE],SERVER_NAME,DATABASE_NAME

FROM

(

SELECT ROW_NUMBER() OVER (ORDER BY DATABASE_NAME,[TYPE],BACKUP_FINISH_DATE) ID,

BACKUP_FINISH_DATE BACKUP_DATE, PHYSICAL_DEVICE_NAME,A.MEDIA_SET_ID,

SERVER_NAME,DATABASE_NAME,[TYPE]

FROM MSDB.DBO.BACKUPSET A JOIN MSDB.DBO.BACKUPMEDIAFAMILY B

ON(A.MEDIA_SET_ID=B.MEDIA_SET_ID)

) BACKUPS

GROUP BY [TYPE],SERVER_NAME,DATABASE_NAME

),

BACKUP_ALL AS

(

SELECT ROW_NUMBER() OVER (ORDER BY DATABASE_NAME,[TYPE],BACKUP_FINISH_DATE) ID,

PHYSICAL_DEVICE_NAME

FROM MSDB.DBO.BACKUPSET A JOIN MSDB.DBO.BACKUPMEDIAFAMILY B

ON(A.MEDIA_SET_ID=B.MEDIA_SET_ID)

)

SELECT SERVER_NAME [SERVER],DATABASE_NAME [DATABASE],BAKUPTYPE=

CASE WHEN [TYPE]=‘D’ THEN ‘FULL’

WHEN [TYPE]=‘I’ THEN ‘DIFFERENTIAL’

WHEN [TYPE]=‘L’ THEN ‘LOG’

WHEN [TYPE]=‘F’ THEN ‘FILE / FILEGROUP’

WHEN [TYPE]=‘G’ THEN ‘DIFFERENTIAL FILE’

WHEN [TYPE]=‘P’ THEN ‘PARTIAL’

WHEN [TYPE]=‘Q’ THEN ‘DIFFERENTIAL PARTIAL’

END,BACKUP_DATE [RECENT BACKUP], PHYSICAL_DEVICE_NAME [LOCATION] FROM BACKUP_RECENT,BACKUP_ALL

WHERE BACKUP_RECENT.ID=BACKUP_ALL.ID AND DATABASE_NAME=@P_DBNAME

ORDER BY BACKUP_DATE

END

END

END

Referecne : http://www.SQLServerCentral.com