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.