Dynamic Stored Procedure with Count, Filter & Paging

 
USE [DBName]
GO
 
/****** Object:  StoredProcedure [dbo].[SPName]    Script Date: 05/04/2010 11:23:08 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE PROCEDURE [dbo].[SPName] 
(
	@CountOnly BIT=NULL,
	@OnlyProductions BIT=NULL,
	@PageNumber int = NULL,
	@PageSize int = NULL,
	@SortExpression varchar(255) = NULL,
	@EventID int= NULL,
	@StartEventTime_From datetime= NULL,
	@StartEventTime_Until datetime= NULL,
	@EndEventTime_From datetime= NULL,
	@EndEventTime_Until datetime= NULL,
 
	@ProductionsOnlineDate_From datetime= NULL,
	@ProductionsOnlineDate_Until datetime= NULL,
	@ProductionsOfflineDate_From datetime= NULL,
	@ProductionsOfflineDate_Until datetime= NULL,
	@ProductionStarttime_From datetime= NULL,
	@ProductionStarttime_Until datetime= NULL,
	@ProductionEndtime_From datetime= NULL,
	@ProductionEndtime_Until datetime= NULL,
 
 
	@FKCode varchar(255)= NULL,
	@ProductionID int= NULL
)
AS
 
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
 
DECLARE @RowBegin int
DECLARE @RowEnd int
DECLARE @EventNull nvarchar(100) 
DECLARE @SQL nvarchar(4000)
DECLARE @SQLResultSet nvarchar(4000)
DECLARE @SQLFilter nvarchar(4000)
DECLARE @Params nvarchar(4000)
 
SET @SQLFilter = ''
 
SET @Params = N'
	@CountOnly BIT=null,
	@OnlyProductions BIT=null,
	@PageNumber int = null,
	@PageSize int = null,
	@sortExpression varchar(255) = null,
	@EventID int= null,
	@StartEventTime_From datetime= null,
	@StartEventTime_Until datetime= null,
	@EndEventTime_From datetime= null,
	@EndEventTime_Until datetime= null,
	@FKCode varchar(255)= null,
	@ProductionID int= null,
	@ProductionsOnlineDate_From datetime= null,
	@ProductionsOnlineDate_Until datetime= null,
	@ProductionsOfflineDate_From datetime= null,
	@ProductionsOfflineDate_Until datetime= null,
	@ProductionStarttime_From datetime= null,
	@ProductionStarttime_Until datetime= null,
	@ProductionEndtime_From datetime= null,
	@ProductionEndtime_Until datetime= null
'
 
BEGIN
 
	--@CountOnly 
	IF @CountOnly IS NULL 
	BEGIN 
		SET @CountOnly = 0
	END
 
	-- OnlyProductions YES/NO 
	SET  @EventNull =''
	IF @OnlyProductions IS NULL 
	BEGIN 
		SET @OnlyProductions = 0
	END
 
	IF @OnlyProductions = 1 
	BEGIN
		SET @EventNull = 'null as '
	END 
 
	--@sortExpression
	IF @sortExpression IS NULL 
	BEGIN 
		SET @sortExpression = 'eventid'
	END
 
	-- Page math
	IF @PageNumber IS NULL OR  @PageNumber < 0
	BEGIN 
		SET @PageNumber = 0
	END
 
	IF @PageSize IS NULL OR  @PageSize < 1
	BEGIN 
		SET @PageSize = 100
	END
	SET @RowBegin = @PageNumber * @PageSize
	SET @RowEnd = @RowBegin + @PageSize
 
 
	-- START @SQLFilter build 
	IF @EventID IS NOT NULL 
	BEGIN 
		SET @SQLFilter = @SQLFilter + ' AND EventID = @EventID ' + char(10)
	END
 
 
	IF @StartEventTime_From IS NOT NULL AND @StartEventTime_Until IS NOT NULL 
	BEGIN 
		SET @SQLFilter = @SQLFilter + ' AND StartEventTime BETWEEN @StartEventTime_From AND   @StartEventTime_Until' + char(10)
	END
 
 
	IF @EndEventTime_From IS NOT NULL AND @EndEventTime_Until IS NOT NULL 
	BEGIN 
		SET @SQLFilter = @SQLFilter + ' AND EndEventTime BETWEEN @EndEventTime_From AND   @EndEventTime_Until' + char(10)
	END
 
 
	IF @FKCode IS NOT NULL 
	BEGIN 
		SET @SQLFilter = @SQLFilter + ' AND FKCode LIKE ''%''+ @FKCode+''%'' ' + char(10)
	END
 
	IF @ProductionID IS NOT NULL 
	BEGIN 
		SET @SQLFilter = @SQLFilter + ' AND productionid = @ProductionID ' + char(10)
	END
 
	IF @ProductionsOnlineDate_From IS NOT NULL AND @ProductionsOnlineDate_Until IS NOT NULL 
	BEGIN 
		SET @SQL = @SQL + ' AND ProductionsOnlineDate BETWEEN @ProductionsOnlineDate_From AND   @ProductionsOnlineDate_Until' + char(10)
	END
 
 
	IF @ProductionsOfflineDate_From IS NOT NULL AND @ProductionsOfflineDate_Until IS NOT NULL 
	BEGIN 
		SET @SQL = @SQL + ' AND ProductionsOfflineDate BETWEEN @ProductionsOfflineDate_From AND   @ProductionsOfflineDate_Until' + char(10)
	END
 
	IF @ProductionStarttime_From IS NOT NULL AND @ProductionStarttime_Until IS NOT NULL 
	BEGIN 
		SET @SQL = @SQL + ' AND ProductionStarttime BETWEEN @ProductionStarttime_From AND   @ProductionStarttime_Until' + char(10)
	END
 
 
	IF @ProductionEndtime_From IS NOT NULL AND @ProductionEndtime_Until IS NOT NULL 
	BEGIN 
		SET @SQL = @SQL + ' AND ProductionEndtime BETWEEN @ProductionEndtime_From AND   @ProductionEndtime_Until' + char(10)
	END
	-- END @SQLFilter build 
 
 
	 IF @CountOnly = 0 
		--@CountOnly = 0 
		BEGIN
			 SET @SQL =
			 'SELECT 
				  *
				FROM   (SELECT  Row_number() OVER (ORDER BY '+ @sortExpression + ' ) AS rowrank , *
					 FROM  (SELECT DISTINCT
							' + @EventNull + ' eventid,
							' + @EventNull + ' starteventtime,
							' + @EventNull + ' endeventtime,
							' + @EventNull + ' EventDurationMinutes,
							' + @EventNull + ' FKcode,
							hallsname,
							' + @EventNull + ' StatusDescription,
							' + @EventNull + ' Availability,
							' + @EventNull + ' EndSale,
							' + @EventNull + ' StartSale,
							' + @EventNull + ' statusid,
							ProductionId,
							ProductionsTitle,
							ProductionsShortTitle,
							ProductionsSubTitle,
							ProductionsProducer,
							ProductionsIntroductionText,
							ProductionsDescription,
							ProductionsWebsite,
							ProductionsTips,
							ProductionsExtra,
							ProductionsSeason,
							ProductionsOnlineDate,
							ProductionsOfflineDate,
							ProductionStarttime,
							ProductionEndtime,
							ProductionsImage169,
							ProductionsImageSquare,
							ProductionsFKCode
	    				FROM  vwEventList
				  			WHERE 1=1 
							AND GetDate () BETWEEN 
							ProductionsOnlineDate AND ProductionsOfflineDate
							'+ @SQLFilter +' 
			         ) AS baseset
                        ) AS SetWithRownumbers
			WHERE  rowrank > ' + Cast( @RowBegin AS varchar) + '
				   AND rowrank <= ' +  Cast( @RowEnd AS varchar) + '
			ORDER BY starteventtime
				   '
		END
	ELSE 
		--@CountOnly <> 0 
		BEGIN 
			SET @SQL = 'SELECT '
 
			IF @OnlyProductions = 1
				BEGIN
					SET @SQL = @SQL + 'COUNT(DISTINCT ProductionId)'
				END
			ELSE
				BEGIN
					SET @SQL = @SQL + 'COUNT(*)'
				END		
 
			SET @SQL = @SQL + 'FROM  vw_Eventlist
								WHERE 1=1 
								'+ @SQLFilter
		END
 
END
 
print @SQL 
 
EXEC sp_executesql @SQL, @Params,
@CountOnly = @CountOnly,
@OnlyProductions = @OnlyProductions,
@PageNumber  = @PageNumber,
@PageSize  = @PageSize,
@SortExpression  = @SortExpression,
@EventID =  @EventID,
@StartEventTime_From  =  @StartEventTime_From ,
@StartEventTime_Until =  @StartEventTime_Until,
@EndEventTime_From  =  @EndEventTime_From ,
@EndEventTime_Until =  @EndEventTime_Until,
 
@ProductionsOnlineDate_From  =  @ProductionsOnlineDate_From ,
@ProductionsOnlineDate_Until =  @ProductionsOnlineDate_Until,
@ProductionsOfflineDate_From  =  @ProductionsOfflineDate_From ,
@ProductionsOfflineDate_Until =  @ProductionsOfflineDate_Until,
@ProductionStarttime_From  =  @ProductionStarttime_From ,
@ProductionStarttime_Until =  @ProductionStarttime_Until,
@ProductionEndtime_From  =  @ProductionEndtime_From ,
@ProductionEndtime_Until =  @ProductionEndtime_Until,
 
@FKCode =  @FKCode,
@ProductionID =  @ProductionID
 
GO

comments

No comments yet.

Sorry, the comment form is closed at this time.