Incomplete Rowset When Calling a Stored Proecdured From SSRS

Question

Incomplete Rowset When Calling a Stored Proecdured From SSRS

Harold J Hunsaker

SSC Rookie

Points: 26
More actions
November 6, 2006 at 1:49 pm

#170988

Hello,
I want to call a stored procedure from a report in SSRS (2005). The proc has some parameters, but I call it with
all values defaulted (to NULL).
I call the proc from the SSRS Data Designer and the Result set shows data in only one column,
and the remaining columns are empty. I get data in all the columns of the result set when I call this same procedure
from the Query Window in the SQL Server Management Console with the same default parameter values (all are NULL).
Any ideas on how to SSRS to return all the data?
Thanks much,
Harold

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Harold J Hunsaker SSC Rookie Points: 26 More actions · Answer 1

I am adding the code for the stored procedure. The procedure creates a temp table, and uses an initial bulk insert to get data for a handful of column in the temp table. Values for the remaining column are obtained via a series of update statements.

I have been able to determine that the report returns values that are stored in the temp table by the bulk insert, all columns updated after the bulk insert are returned as empty. The procedure works correctly when called from the SQL Server Query Window.

Any help is appreciated...

-- File: sp_GetProgramsWatchedByDateRange.sql

-- Desc: Returns EDP program and related channel (i.e., provider) information from the IPTV Data warehouse.

-- Note that some of that data used by this procedure are obtained from the RMS_EPG database

-- which is created by an application (loadEPG) that loads the EPG data from a GLF format XML file.

-- Auth: H Hunsaker

-- Date: 11/06/2006

-- Example invocation

-- EXEC dbo.sp_GetProgramsWatchedByDateRange ...

-- Arguments/Parameters:

-- Parameter Name Type Description

-- 3. StartDate datetime First date of reporting period

-- 4. EndDate datetime Last date of reporting period

-- TerseMode bit Return all columns? (1 = no, 0 = yes)

-- 5. AsXML bit Resultset format (0 = standard, 1 = XML)

-- 6. Debug bit Debug mode (0 = off, 1 = on). Currently disabled

IF OBJECT_ID (N'dbo.sp_GetProgramsWatchedByDateRange') IS NOT NULL

DROP PROCEDURE dbo.sp_GetProgramsWatchedByDateRange

GO

CREATE PROCEDURE dbo.sp_GetProgramsWatchedByDateRange

@StartDate datetime = NULL,

@EndDate datetime = NULL,

@TerseMode bit = 0,

@AsXML bit = 0,

@Debug bit = 0

AS

-- Notes: Much of the program content (roles, flags, etc.) that we want is not stored in the IPTV data warehouse.

-- So I am going to the RMS_EPG database to obtain that information.

-- We will have to ensure that the 2 databases are generated at the same or a matching time

-- in order to to ensure that all programID values in the data warehouse can be located in the RMS_EPG database.

-- Debug code for testing

-- DECLARE @StartDate datetime

-- DECLARE @EndDate datetime

-- DECLARE @TerseMode bit

--SET @StartDate = NULL

--SET @EndDate = NULL

--SET @TerseMode = 1

SET NOCOUNT ON

CREATE TABLE #programWatched

(

--IPTV device ID

tdeviceId uniqueidentifier NULL,

taccountId uniqueidentifier NULL,

-- Basic program information

tprogram int NULL, -- programID from EPG XML, needed to access program data in the RMS_EPG db.

tprogramId uniqueidentifier NULL, -- programID generated by IPTV

tprogramTitle varchar(150) NULL,

tprogramEpisodeTitle varchar(100) NULL,

tprogramDescription varchar(500) NULL,

toriginDateTime datetime NULL,

tduration bigint NULL,

tprogramType nvarchar(100) NULL,

tchannelCallName nvarchar(20) NULL,

--Rating

programMPAARating varchar(50) NULL,

programVCHIPRating varchar(50) NULL,

programMPAARatingVal smallint NULL,

programVChipRatingVal smallint NULL,

-- Categories

programGenre varchar(50) NULL,

programCategory1 varchar(50) NULL,

programCategory2 varchar(50) NULL,

programCategory3 varchar(50) NULL,

programCategory4 varchar(50) NULL,

-- Roles

programActor1FirstName varchar(50) NULL,

programActor1LastName varchar(50) NULL,

programActor1 varchar(100) NULL,

programActor2FirstName varchar(50) NULL,

programActor2LastName varchar(50) NULL,

programActor2 varchar(100) NULL,

programActor3FirstName varchar(50) NULL,

programActor3LastName varchar(50) NULL,

programActor3 varchar(100) NULL,

programActor4FirstName varchar(50) NULL,

programActor4LastName varchar(50) NULL,

programActor4 varchar(100) NULL,

programActor5FirstName varchar(50) NULL,

programActor5LastName varchar(50) NULL,

programActor5 varchar(100) NULL,

programActor6FirstName varchar(50) NULL,

programActor6LastName varchar(50) NULL,

programActor6 varchar(100) NULL,

programActor7FirstName varchar(50) NULL,

programActor7LastName varchar(50) NULL,

programActor7 varchar(100) NULL,

programActor8FirstName varchar(50) NULL,

programActor8LastName varchar(50) NULL,

programActor8 varchar(100) NULL,

programDirectorFirstName varchar(50) NULL,

programDirectorLastName varchar(50) NULL,

programDirector varchar(100) NULL,

programWriterFirstName varchar(50) NULL,

programWriterLastName varchar(50) NULL,

programWriter varchar(100) NULL,

programProducerFirstName varchar(50) NULL,

programProducerLastName varchar(50) NULL,

programProducer varchar(100) NULL,

-- Flags

ClosedCaption bit NULL,

InStereo bit NULL,

Repeats bit NULL,

New bit NULL,

Live bit NULL,

Taped bit NULL,

Subtitled bit NULL,

SAP bit NULL,

ThreeD bit NULL,

Letterbox bit NULL,

HDTV bit NULL,

Dolby bit NULL,

DVS bit NULL,

FlagOrdinalValue smallint NULL,

-- Channel

tchannelId int NULL,

callLetters varchar(20) NULL,

displayName varchar(50) NULL,

type varchar(50) NULL,

networkAffiliation varchar(50) NULL

&nbsp

-- I store the program watching data in a temp table because

-- data from the VIL and the Sandbox that were used to test this procedure were either incomplete or invalid.

-- Use of a temp table with a series of updates allow me more control over the result set.

IF @StartDate IS NOT NULL AND @EndDate IS NOT NULL

INSERT INTO #programWatched (

tdeviceId,

tprogramId,

--tprogramTitle,

--tprogramEpisodeTitle,

toriginDateTime,

tduration,

--tprogramType,

--tchannelCallName,

ClosedCaption,

InStereo,

Repeats,

New,

Live,

Taped,

Subtitled,

SAP,

ThreeD,

Letterbox,

HDTV,

Dolby,

DVS

&nbsp

SELECT pw.DeviceID,

pw.programID,

--epg.program,

--epg.programTitle,

--epg.programEpisodeTitle,

pw.originTime AS 'When Watched',

pw.Duration AS 'Duration Seconds',

--epg.programType,

--epg.channelCallName,

0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 -- program flag values default to zero, as we do not want NULL values.

FROM DW_EventClientProgramWatched pw

WHERE programID IS NOT NULL AND programID != '00000000-0000-0000-0000-000000000000' -- These values should not occur, but they did in the test system

AND originTime BETWEEN @StartDate AND @EndDate

ELSE

INSERT INTO #programWatched (

tdeviceId,

tprogramId,

--tprogramTitle,

--tprogramEpisodeTitle,

toriginDateTime,

tduration,

--tprogramType,

--tchannelCallName,

ClosedCaption,

InStereo,

Repeats,

New,

Live,

Taped,

Subtitled,

SAP,

ThreeD,

Letterbox,

HDTV,

Dolby,

DVS

&nbsp

SELECT pw.DeviceID,

pw.programID,

--epg.program,

--epg.programTitle,

--epg.programEpisodeTitle,

pw.originTime AS 'When Watched',

pw.Duration AS 'Duration Seconds',

--epg.programType,

--epg.channelCallName,

0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 -- program flag values default to zero, as we do not want NULL values.

FROM DW_EventClientProgramWatched pw

WHERE programID IS NOT NULL AND programID != '00000000-0000-0000-0000-000000000000' -- These values should not occur, but they did in the test system

-- AccountId/SubscriberId

UPDATE #programWatched

SET taccountId = (SELECT accountId

FROM DW_BRDB_bm_device d

WHERE d.deviceId = tdeviceId)

-- program (this is the integer program ID stored in the EPG XML, not to be confused with the IPTV programId)

-- a program can occur on multiple channels, so we filter channels where scheduleTime <= originTime <= scheculeTime + durationSecs

UPDATE #programWatched

SET tchannelCallName = (SELECT TOP 1 channelCallName

FROM DW_EPG EPG

WHERE tprogramId = EPG.programId AND toriginDateTime BETWEEN scheduleTime AND DATEADD(s, epg.durationSecs, epg.scheduleTime))

UPDATE #programWatched

SET tprogram = (SELECT TOP 1 program FROM DW_EPG EPG WHERE tprogramId = EPG.programId AND tchannelCallName = channelCallName),

tprogramTitle = (SELECT TOP 1 programTitle FROM DW_EPG EPG WHERE tprogramId = EPG.programId AND tchannelCallName = channelCallName),

tprogramEpisodeTitle = (SELECT TOP 1 programEpisodeTitle FROM DW_EPG EPG WHERE tprogramId = EPG.programId AND tchannelCallName = channelCallName),

tprogramType = (SELECT TOP 1 programType FROM DW_EPG EPG WHERE tprogramId = EPG.programId AND tchannelCallName = channelCallName)

-- Rating (otained from programValues, can also be obtained from programFlags)

UPDATE #programWatched

SET programMPAARating = (SELECT TOP 1 programValue

FROM RMS_EPG..programValue pv

WHERE tprogram = pv.programID AND pv.programValueTypeId = 9)

UPDATE #programWatched

SET programMPAARatingVal = CASE programMPAARating

WHEN 'G' THEN 10

WHEN 'PG' THEN 25

WHEN 'PG-13' THEN 30

WHEN 'R' THEN 35

WHEN 'NC-17' THEN 50

WHEN 'NRAO' THEN 60

WHEN 'NR' THEN 0

ELSE 0

END

UPDATE #programWatched

SET programVChipRating = (SELECT TOP 1 programValue

FROM RMS_EPG..programValue pv

WHERE tprogram = pv.programID AND pv.programValueTypeId = 8)

UPDATE #programWatched

SET programVChipRatingVal = CASE programVChipRating

WHEN 'TV-Y' THEN 10

WHEN 'TV-Y7' THEN 20

WHEN 'TV-G' THEN 35

WHEN 'TV-PG' THEN 40

WHEN 'TV-14' THEN 45

WHEN 'TV-MA' THEN 60

ELSE 0

END

-- Genre

UPDATE #programWatched

SET programGenre = (SELECT TOP 1 programCategoryTypeValue

FROM RMS_EPG..programCategory pc

INNER JOIN RMS_EPG..programSubCategoryType psct ON psct.programSubCategoryTypeId = pc.programCategoryId

INNER JOIN RMS_EPG..programCategoryType pct ON pct.programCategoryTypeId = psct.programCategoryTypeId

WHERE tprogram = pc.programID)

-- Categories

UPDATE #programWatched

SET programCategory1 = (SELECT TOP 1 programSubCategoryTypeValue

FROM RMS_EPG..programCategory pc

INNER JOIN RMS_EPG..programSubCategoryType pct ON pct.programSubCategoryTypeId = pc.programCategoryId

WHERE tprogram = pc.programID)

UPDATE #programWatched

SET programCategory2 = (SELECT TOP 1 programSubCategoryTypeValue

FROM RMS_EPG..programCategory pc

INNER JOIN RMS_EPG..programSubCategoryType pct ON pct.programSubCategoryTypeId = pc.programCategoryId

WHERE tprogram = pc.programID AND programSubCategoryTypeValue NOT IN (programCategory1))

UPDATE #programWatched

SET programCategory3 = (SELECT TOP 1 programSubCategoryTypeValue

FROM RMS_EPG..programCategory pc

INNER JOIN RMS_EPG..programSubCategoryType pct ON pct.programSubCategoryTypeId = pc.programCategoryId

WHERE tprogram = pc.programID AND programSubCategoryTypeValue NOT IN (programCategory1, programCategory2))

UPDATE #programWatched

SET programCategory4 = (SELECT TOP 1 programSubCategoryTypeValue

FROM RMS_EPG..programCategory pc

INNER JOIN RMS_EPG..programSubCategoryType pct ON pct.programSubCategoryTypeId = pc.programCategoryId

WHERE tprogram = pc.programID AND programSubCategoryTypeValue NOT IN (programCategory1, programCategory2, programCategory3))

-- Roles

UPDATE #programWatched

SET programDirectorFirstName = (SELECT TOP 1 programRoleFirstName

FROM RMS_EPG..programRoleName prn

INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId

WHERE tprogram = pr.programID AND pr.programRoleTypeId = 2)

UPDATE #programWatched

SET programDirectorLastName = (SELECT TOP 1 programRoleLastName

FROM RMS_EPG..programRoleName prn

INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId

WHERE tprogram = pr.programID AND pr.programRoleTypeId = 2)

UPDATE #programWatched

SET programDirector = programDirectorLastName + ' , ' + programDirectorFirstName

UPDATE #programWatched

SET programWriterFirstName = (SELECT TOP 1 programRoleFirstName

FROM RMS_EPG..programRoleName prn

INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId

WHERE tprogram = pr.programID AND pr.programRoleTypeId = 7)

UPDATE #programWatched

SET programWriterLastName = (SELECT TOP 1 programRoleLastName

FROM RMS_EPG..programRoleName prn

INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId

WHERE tprogram = pr.programID AND pr.programRoleTypeId = 7)

UPDATE #programWatched

SET programWriter = programWriterLastName + ' , ' + programWriterFirstName

UPDATE #programWatched

SET programProducerFirstName = (SELECT TOP 1 programRoleFirstName

FROM RMS_EPG..programRoleName prn

INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId

WHERE tprogram = pr.programID AND pr.programRoleTypeId = 6)

UPDATE #programWatched

SET programProducerLastName = (SELECT TOP 1 programRoleLastName

FROM RMS_EPG..programRoleName prn

INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId

WHERE tprogram = pr.programID AND pr.programRoleTypeId = 6)

UPDATE #programWatched

SET programProducer = programProducerLastName + ' , ' + programProducerFirstName

UPDATE #programWatched

SET programActor1FirstName = (SELECT TOP 1 programRoleFirstName

FROM RMS_EPG..programRoleName prn

INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId

WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1)

UPDATE #programWatched

SET programActor1LastName = (SELECT TOP 1 programRoleLastName

FROM RMS_EPG..programRoleName prn

INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId

WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1)

UPDATE #programWatched

SET programActor1 = programActor1LastName + ' , ' + programActor1FirstName

UPDATE #programWatched

SET programActor2FirstName = (SELECT TOP 1 programRoleFirstName

FROM RMS_EPG..programRoleName prn

INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId

WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1

AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName))

UPDATE #programWatched

SET programActor2LastName = (SELECT TOP 1 programRoleLastName

FROM RMS_EPG..programRoleName prn

INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId

WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1

AND programRoleLastName NOT IN (programActor1LastName) AND programRoleLastName NOT IN (programActor1LastName))

UPDATE #programWatched

SET programActor2 = programActor2LastName + ' , ' + programActor2FirstName

UPDATE #programWatched

SET programActor3FirstName = (SELECT TOP 1 programRoleFirstName

FROM RMS_EPG..programRoleName prn

INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId

WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1

AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName)

AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName))

UPDATE #programWatched

SET programActor3LastName = (SELECT TOP 1 programRoleLastName

FROM RMS_EPG..programRoleName prn

INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId

WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1

AND programRoleLastName NOT IN (programActor1LastName) AND programRoleLastName NOT IN (programActor1LastName)

AND programRoleLastName NOT IN (programActor2LastName) AND programRoleLastName NOT IN (programActor2LastName))

UPDATE #programWatched

SET programActor3 = programActor3LastName + ' , ' + programActor3FirstName

UPDATE #programWatched

SET programActor4FirstName = (SELECT TOP 1 programRoleFirstName

FROM RMS_EPG..programRoleName prn

INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId

WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1

AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName)

AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName)

AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName))

UPDATE #programWatched

SET programActor4LastName = (SELECT TOP 1 programRoleLastName

FROM RMS_EPG..programRoleName prn

INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId

WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1

AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName)

AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName)

AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName))

UPDATE #programWatched

SET programActor4 = programActor4LastName + ' , ' + programActor4FirstName

UPDATE #programWatched

SET programActor5FirstName = (SELECT TOP 1 programRoleFirstName

FROM RMS_EPG..programRoleName prn

INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId

WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1

AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName)

AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName)

AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName)

AND programRoleFirstName NOT IN (programActor4FirstName) AND programRoleLastName NOT IN (programActor4LastName))

UPDATE #programWatched

SET programActor5LastName = (SELECT TOP 1 programRoleLastName

FROM RMS_EPG..programRoleName prn

INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId

WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1

AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName)

AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName)

AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName)

AND programRoleFirstName NOT IN (programActor4FirstName) AND programRoleLastName NOT IN (programActor4LastName))

UPDATE #programWatched

SET programActor5 = programActor5LastName + ' , ' + programActor5FirstName

UPDATE #programWatched

SET programActor6FirstName = (SELECT TOP 1 programRoleFirstName

FROM RMS_EPG..programRoleName prn

INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId

WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1

AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName)

AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName)

AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName)

AND programRoleFirstName NOT IN (programActor4FirstName) AND programRoleLastName NOT IN (programActor4LastName)

AND programRoleFirstName NOT IN (programActor5FirstName) AND programRoleLastName NOT IN (programActor5LastName))

UPDATE #programWatched

SET programActor6LastName = (SELECT TOP 1 programRoleLastName

FROM RMS_EPG..programRoleName prn

INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId

WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1

AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName)

AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName)

AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName)

AND programRoleFirstName NOT IN (programActor4FirstName) AND programRoleLastName NOT IN (programActor4LastName)

AND programRoleFirstName NOT IN (programActor5FirstName) AND programRoleLastName NOT IN (programActor5LastName))

UPDATE #programWatched

SET programActor6 = programActor6LastName + ' , ' + programActor6FirstName

UPDATE #programWatched

SET programActor7FirstName = (SELECT TOP 1 programRoleFirstName

FROM RMS_EPG..programRoleName prn

INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId

WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1

AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName)

AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName)

AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName)

AND programRoleFirstName NOT IN (programActor4FirstName) AND programRoleLastName NOT IN (programActor4LastName)

AND programRoleFirstName NOT IN (programActor5FirstName) AND programRoleLastName NOT IN (programActor5LastName)

AND programRoleFirstName NOT IN (programActor6FirstName) AND programRoleLastName NOT IN (programActor6LastName))

UPDATE #programWatched

SET programActor7LastName = (SELECT TOP 1 programRoleLastName

FROM RMS_EPG..programRoleName prn

INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId

WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1

AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName)

AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName)

AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName)

AND programRoleFirstName NOT IN (programActor4FirstName) AND programRoleLastName NOT IN (programActor4LastName)

AND programRoleFirstName NOT IN (programActor5FirstName) AND programRoleLastName NOT IN (programActor5LastName)

AND programRoleFirstName NOT IN (programActor6FirstName) AND programRoleLastName NOT IN (programActor6LastName))

UPDATE #programWatched

SET programActor7 = programActor7LastName + ' , ' + programActor7FirstName

UPDATE #programWatched

SET programActor8FirstName = (SELECT TOP 1 programRoleFirstName

FROM RMS_EPG..programRoleName prn

INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId

WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1

AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName)

AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName)

AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName)

AND programRoleFirstName NOT IN (programActor4FirstName) AND programRoleLastName NOT IN (programActor4LastName)

AND programRoleFirstName NOT IN (programActor5FirstName) AND programRoleLastName NOT IN (programActor5LastName)

AND programRoleFirstName NOT IN (programActor6FirstName) AND programRoleLastName NOT IN (programActor6LastName)

AND programRoleFirstName NOT IN (programActor7FirstName) AND programRoleLastName NOT IN (programActor7LastName))

UPDATE #programWatched

SET programActor8LastName = (SELECT TOP 1 programRoleLastName

FROM RMS_EPG..programRoleName prn

INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId

WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1

AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName)

AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName)

AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName)

AND programRoleFirstName NOT IN (programActor4FirstName) AND programRoleLastName NOT IN (programActor4LastName)

AND programRoleFirstName NOT IN (programActor5FirstName) AND programRoleLastName NOT IN (programActor5LastName)

AND programRoleFirstName NOT IN (programActor6FirstName) AND programRoleLastName NOT IN (programActor6LastName)

AND programRoleFirstName NOT IN (programActor7FirstName) AND programRoleLastName NOT IN (programActor7LastName))

UPDATE #programWatched

SET programActor8 = programActor8LastName + ' , ' + programActor8FirstName

-- Channel (provider) Call Letters, Display Name and Type

-- Is this correct? Should we get the channelId from the schedule table?

-- Is this efficient? View execution plan

UPDATE #programWatched

SET tchannelId = (SELECT TOP 1 c.channelId

FROM RMS_EPG..channel c

INNER JOIN RMS_EPG..schedule s on s.channelID = c.channelID

WHERE s.programId = tprogram)

UPDATE #programWatched

SET callLetters = (SELECT TOP 1 c.channelCallLetters

FROM RMS_EPG..channel c

INNER JOIN RMS_EPG..schedule s on s.channelID = c.channelID

WHERE s.programId = tprogram and s.channelId = tchannelId)

UPDATE #programWatched

SET displayName = (SELECT TOP 1 c.channelDisplayName

FROM RMS_EPG..channel c

JOIN RMS_EPG..schedule s on s.channelID = c.channelID

WHERE s.programId = tprogram and s.channelId = tchannelId)

UPDATE #programWatched

SET type = (SELECT TOP 1 c.channelType

FROM RMS_EPG..channel c

INNER JOIN RMS_EPG..schedule s on s.channelID = c.channelID

WHERE s.programId = tprogram and s.channelId = tchannelId)

UPDATE #programWatched

SET networkAffiliation = (SELECT TOP 1 c.channelNetworkAffiliation

FROM RMS_EPG..channel c

INNER JOIN RMS_EPG..schedule s on s.channelID = c.channelID

WHERE s.programId = tprogram and s.channelId = tchannelId)

IF @TerseMode = 0

SELECT *

FROM #programWatched

ORDER BY toriginDateTime

ELSE

-- Get only Genre, title, show date/time, rating, call letters

SELECT tDeviceId, tprogramTitle, tprogramEpisodeTitle, programGenre, toriginDateTime, programMPAARating, programVCHIPRating, tchannelCallName

FROM #programWatched

ORDER BY toriginDateTime

DROP TABLE #programWatched

SET NOCOUNT OFF

GO