Sep 8

Written by: Travis Whidden
9/8/2007 5:19 PM

I decided to throw up some code that I like to use, but hate re-writting.  This is how I do some of the dynamic SQL statements.  If you think there is a better way of doing it, please let me know.  This has worked very well for me so far.

 

USE GO /****** Object: StoredProcedure [dbo].[RS_Reservations_Get_List_By_DateRange] Script Date: 09/08/2007 17:14:28 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ------------------------------------------------------------------------------------------------------------------------ -- Date Created: Tuesday, November 18, 2006 -- Created By: Travis ------------------------------------------------------------------------------------------------------------------------ CREATE PROCEDURE [dbo].[RS_Reservations_Get_List_By_DateRange] @StartDate datetime, @EndDate datetime, @CompanyID uniqueidentifier, @LocationID uniqueidentifier, @ReservationObjectID uniqueidentifier AS Declare @BaseSQL nvarchar(900) Declare @WhereSQL nvarchar(900) Declare @FinalSQl nvarchar(900) set @BaseSQL = ' SELECT [ReservationID], [FK_ReserveObjectID], [UTC_ReservationDateTime], [ReservedMinutes], [HoldExpires], [Canceled], [UTC_Created], [UTC_Updated], [FK_CreatedBy], [FK_CanceledBy], [FK_SalesOrderItemID] FROM [dbo].[viewReservationAssociations] ' /* Set the initial value */ set @WhereSQL = '' /* Build the rest of the where clause dynamicly */ if @StartDate IS NOT NULL AND @EndDate IS NOT NULL begin if @WhereSQL = '' begin set @WhereSQL = ' WHERE ' end else begin set @WhereSQL = @WhereSQL + ' AND ' end set @WhereSQL = @WhereSQL + ' [UTC_ReservationDateTime] between ''' + Convert(varchar(20), @StartDate) + ''' AND ''' + Convert(varchar(20), @EndDate) + '''' end if @CompanyID IS NOT NULL begin if @WhereSQL = '' begin set @WhereSQL = ' WHERE ' end else begin set @WhereSQL = @WhereSQL + ' AND ' end set @WhereSQL = @WhereSQL + ' [CompanyID] = ''' + Convert(varchar(40), @CompanyID) + '''' end if @LocationID IS NOT NULL begin if @WhereSQL = '' begin set @WhereSQL = ' WHERE ' end else begin set @WhereSQL = @WhereSQL + ' AND ' end set @WhereSQL = @WhereSQL + ' [LocationID] = ''' + Convert(varchar(40), @LocationID) + '''' end if @ReservationObjectID IS NOT NULL begin if @WhereSQL = '' begin set @WhereSQL = ' WHERE ' end else begin set @WhereSQL = @WhereSQL + ' AND ' end set @WhereSQL = @WhereSQL + ' [ReserveObjectID] = ''' + Convert(varchar(40), @ReservationObjectID) + '''' end /* Create the final SQL string */ set @FinalSQL = @BaseSQL + @WhereSQL + ' ORDER BY [UTC_ReservationDateTime] DESC ' /* Execute the SQL */ EXEC dbo.sp_executeSQL @statement = @FinalSQL

Tags: