I have two stored procedures, one of which is SelectHeadlineData into which four values are passed as parameters:

@StartDate, @EndDate, @ReportType, @userName

SelectHeadlineData will execute one or three times to populate table RTTReportHelper with data specific to the value of @ReportType and between the two date parameters.

If SelectHeadlineData runs once, then the @ReportType value passed to it will only ever be 0 or 1 and the table RTTReportHelper can be cleared.

If SelectHeadlineData runs three times, the 3 values of @ReportType passed to it will be 1, 2 and 3.

SelectHeadlineData will populate three tables on a page with three different sets of headline data based on the value of @ReportType.

On each line of the three tables there is a hyperlink and clicking on any given hyperlink will open a subsequent page with further drill down data.

Clicking on a hyperlink will execute stored procedure SelectDrilldownData which will select data from table RTTReportHelper by @userName so no need to generate the data again because it should already exist.

The problem I have is that each time SelectHeadlineData executes, it clears table RTTReportHelper by @userName value.

This is fine if SelectHeadlineData executes only once but if it executes three times then each each execution clears the table so after the third and last execution the only data that remains in the table is the data relevant to the third table and the value of 3 for @ReportType.

Is there a way of controlling the clearing of RTTReportHelper when SelectHeadlineData executes three times so that the data for all three executions of SelectHeadlineData remains in place and is available for selection/viewing on the page?

I've tried creating a helper table containing two columns; Username and LastReportTypeValue and cleared the table conditionally if the highest value for LastReportTypeValue by Username wasn't 3 but this didn't work.

The stored procedures look like this

CREATE PROCEDURE [dbo].[SelectHeadlineData]
    @StartDate datetime,
    @EndDate datetime,
    @ReportType int = 0,
    @Username varchar(100)
AS
    DELETE FROM dbo.RTTReportHelper 
    WHERE Username = @userName

    EXEC dbo.PopulateRTTReportHelper @StartDate, @EndDate, @ReportType, @userName

    SELECT * 
    FROM dbo.RTTReportHelper 
    WHERE Username = @userName


CREATE PROCEDURE [dbo].[SelectDrilldownData]
    @Username varchar(100)

    SELECT * 
    FROM dbo.RTTReportHelper 
    WHERE Username = @userName  

I have two stored procedures, one of which is SelectHeadlineData into which four values are passed as parameters:

@StartDate, @EndDate, @ReportType, @userName

SelectHeadlineData will execute one or three times to populate table RTTReportHelper with data specific to the value of @ReportType and between the two date parameters.

If SelectHeadlineData runs once, then the @ReportType value passed to it will only ever be 0 or 1 and the table RTTReportHelper can be cleared.

If SelectHeadlineData runs three times, the 3 values of @ReportType passed to it will be 1, 2 and 3.

SelectHeadlineData will populate three tables on a page with three different sets of headline data based on the value of @ReportType.

On each line of the three tables there is a hyperlink and clicking on any given hyperlink will open a subsequent page with further drill down data.

Clicking on a hyperlink will execute stored procedure SelectDrilldownData which will select data from table RTTReportHelper by @userName so no need to generate the data again because it should already exist.

The problem I have is that each time SelectHeadlineData executes, it clears table RTTReportHelper by @userName value.

This is fine if SelectHeadlineData executes only once but if it executes three times then each each execution clears the table so after the third and last execution the only data that remains in the table is the data relevant to the third table and the value of 3 for @ReportType.

Is there a way of controlling the clearing of RTTReportHelper when SelectHeadlineData executes three times so that the data for all three executions of SelectHeadlineData remains in place and is available for selection/viewing on the page?

I've tried creating a helper table containing two columns; Username and LastReportTypeValue and cleared the table conditionally if the highest value for LastReportTypeValue by Username wasn't 3 but this didn't work.

The stored procedures look like this

CREATE PROCEDURE [dbo].[SelectHeadlineData]
    @StartDate datetime,
    @EndDate datetime,
    @ReportType int = 0,
    @Username varchar(100)
AS
    DELETE FROM dbo.RTTReportHelper 
    WHERE Username = @userName

    EXEC dbo.PopulateRTTReportHelper @StartDate, @EndDate, @ReportType, @userName

    SELECT * 
    FROM dbo.RTTReportHelper 
    WHERE Username = @userName


CREATE PROCEDURE [dbo].[SelectDrilldownData]
    @Username varchar(100)

    SELECT * 
    FROM dbo.RTTReportHelper 
    WHERE Username = @userName  

• sql
• sql-server
• t-sql

Share Improve this question Follow edited Mar 3 at 17:16 marc_s 757k184184 gold badges1.4k1.4k silver badges1.5k1.5k bronze badges asked Mar 3 at 15:16 stonypaulstonypaul 70511 gold badge88 silver badges2424 bronze badges 2

• 1 Why don't you just add a @ClearData bit parameter, and in the procedure do IF @ClearData = 1 DELETE ... then only pass that parameter on the first of the three calls? – Charlieface Commented Mar 3 at 15:24
• do you control the calls or are they called randomly by the report server? – siggemannen Commented Mar 3 at 20:47

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 2

Just slap an if statement around the delete:

if (@ReportType <= 1)
begin
  DELETE FROM dbo.RTTReportHelper WHERE Username = @userName
end

Using just one table wouldn't work as when the stored procedure was executed three times, the table was being cleared each resulting in the data being cleared from the table on each execution. This made the days unavailable for future retrievals so I created three tables to hold the three sets of data and this solved the problem