Generic Dynamically Constructed SQL string

 

USE [MyDB]
GO
/****** Object:  StoredProcedure [dbo].[GetEventsByCriteria]    Script Date: 02/02/2011 01:32:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET DATEFORMAT DMY
GO
create PROCEDURE [dbo].[GetEventsByCriteria6]
(
  @Criteria nvarchar(255), 
  @Keyword nvarchar(255),
  @Course nvarchar(255), 
  @EventStartDate nvarchar(25),
  @EventEndDate nvarchar(25),
  @DateOfEntryStart nvarchar(25),
  @DateOfEntryEnd nvarchar(25)  
)

As
DECLARE @SQLString NVARCHAR(500)
DECLARE @SQLStr1 NVARCHAR(200)
DECLARE @SQLStr2 NVARCHAR(200)
DECLARE @SQLStr3 NVARCHAR(200)
DECLARE @SQLStr4 NVARCHAR(200)

/* Set column list. CHAR(13) is a carriage return, line feed.*/
SET @SQLString = N'SELECT * FROM [EVENTS] ' + CHAR(13)

/* Set WHERE clause. */
SET @SQLStr1 =  N' [EventStartDate]>=''' + @EventStartDate + ''''
+ N' AND [EventEndDate]<=''' + @EventEndDate + ''''


SET @SQLStr2 =  N' [DateOfEntry]>=''' + @DateOfEntryStart + ''''
+ N' AND DateOfEntry<=''' + @DateOfEntryEnd+ ''''

SET @SQLStr3 =  N' [EventTitle] LIKE ''%' + @Keyword + N'%'''

SET @SQLStr4 =  N' [Course] LIKE ''%' + @Course + N'%'''


/* Set ORDER clause. */
if (charindex('WHERE',@SQLString)=0 and charindex('EventDate',@Criteria)<>0)  
SET @SQLString = @SQLString + N' WHERE ' + @SQLStr1
else if (charindex('WHERE',@SQLString)<>0 and charindex('EventDate',@Criteria)<>0)
  SET @SQLString = @SQLString + N' AND ' + @SQLStr1

if (charindex('WHERE',@SQLString)=0 and charindex('DateOfEntry',@Criteria)<>0)
  SET @SQLString = @SQLString + N' WHERE ' + @SQLStr2
else if (charindex('WHERE',@SQLString)<>0 and charindex('DateOfEntry',@Criteria)<>0)
  SET @SQLString = @SQLString + N' AND ' + @SQLStr2

if (charindex('WHERE',@SQLString)=0 and charindex('Keyword',@Criteria)<>0)
  SET @SQLString = @SQLString + N' WHERE ' + @SQLStr3
else if (charindex('WHERE',@SQLString)<>0 and charindex('Keyword',@Criteria)<>0)
  SET @SQLString = @SQLString + N' AND ' + @SQLStr3

if (charindex('WHERE',@SQLString)=0 and charindex('Course',@Criteria)<>0)
  SET @SQLString = @SQLString + N' WHERE ' + @SQLStr4
else if (charindex('WHERE',@SQLString)<>0 and charindex('Course',@Criteria)<>0)
  SET @SQLString = @SQLString + N' AND ' + @SQLStr4

SET @SQLString = @SQLString + N' ORDER BY [EventStartDate] DESC'
-- CONVERT(datetime, @eventstartdate, 103)

print @SQLString
EXEC sp_executesql @SQLString
--CONVERT(varchar(8), ctdate, 112)