Data Driven Subscriptions in SQL RS Standard

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

/*
PROCEDURE DESCRIPTION:
  This procedure will replace the predefined fields for a SQL Reporting Services
        Subscription allowing a "DATA DRIVEN SUBSCRIPTION"

INPUT:
  @scheduleIDThe Job Name in SQL Server 2000
  @emailTO The TO address of the email
  @emailCC The Carbon Copy address of the email
  @emailBCC The Blind Copy address of the email
  @emailReplyTO The Reply TO address of the email
  @emailBODY Any text that you want in the email body
  @param1 The value of the parameter defined as P1 in the subscription

OUTPUT:
  None

WRITTEN BY:
  Jason L. Selburg
  HeroTheCat@indy.rr.com

NOTES:
  This procedure does not have error checking or return codes included.
Feel free to modify this code as you see fit.

*/

CREATE    procedure dbo.usp_data_driven_subscription
( @scheduleID uniqueidentifier,
  @emailTO varchar (2000) = '',
  @emailCC varchar (2000) = '',
  @emailBCC varchar (2000) = '',
  @emailReplyTO varchar (2000) = '',
  @emailBODY varchar (8000) = '',
  @param1 varchar (256) = ''
)
as

DECLARE
@ptrval binary(16), 
@PARAMptrval binary(16),
@TOpos int, 
@CCpos int, 
@BCCpos int, 
@RTpos int, 
    @BODYpos int,
@PARAM1Pos int, 
@length int,
@subscriptionID uniqueidentifier

-- set the subscription ID
SELECT @subscriptionID = SubscriptionID
FROM ReportSchedule WHERE ScheduleID = @scheduleID

-- set the text point for this record
SELECT @ptrval = TEXTPTR(ExtensionSettings) 
FROM Subscriptions WHERE SubscriptionID = @subscriptionID

-- set the text point for this record
SELECT @PARAMptrval = TEXTPTR(Parameters) 
FROM Subscriptions WHERE SubscriptionID = @subscriptionID

-- set the start position for the TO Address
SELECT @TOpos = patindex('%|TO|%', ExtensionSettings) - 1
FROM Subscriptions WHERE SubscriptionID = @subscriptionID

IF isnull(@TOpos, '') <> '' and @TOpos > 0 and len(@emailTo) > 0
-- change the TO address
UPDATETEXT Subscriptions.ExtensionSettings 
@ptrval 
@TOpos
4
@emailTo

-- set the start position for the CC Address
SELECT @CCpos = patindex('%|CC|%', ExtensionSettings) - 1
FROM Subscriptions WHERE SubscriptionID = @subscriptionID

IF isnull(@CCpos, '') <> '' and @CCpos > 0 and len(@emailCC) > 0
-- change the TO address
UPDATETEXT Subscriptions.ExtensionSettings 
@ptrval 
@CCpos
4
@emailCC

-- set the start position for the BCC Address
SELECT @BCCpos = patindex('%|BC|%', ExtensionSettings) - 1
FROM Subscriptions WHERE SubscriptionID = @subscriptionID

IF isnull(@BCCpos, '') <> '' and @BCCpos > 0 and len(@emailBCC) > 0
-- change the TO address
UPDATETEXT Subscriptions.ExtensionSettings 
@ptrval 
@BCCpos
4
@emailBCC

-- set the start position for the REPLY TO Address
SELECT @RTpos = patindex('%|RT|%', ExtensionSettings) - 1
FROM Subscriptions WHERE SubscriptionID = @subscriptionID

IF isnull(@RTpos, '') <> '' and @RTpos > 0 and len(@emailReplyTO) > 0
-- change the REPLY TO address
UPDATETEXT Subscriptions.ExtensionSettings 
@ptrval 
@RTpos
4
@emailReplyTO

-- set the start position for the BODY Text
SELECT @BODYpos = patindex('%|BD|%', ExtensionSettings) - 1
FROM Subscriptions WHERE SubscriptionID = @subscriptionID

IF isnull(@BODYpos, '') <> '' and @BODYpos > 0 and len(@emailBODY) > 0
-- change the REPLY TO address
UPDATETEXT Subscriptions.ExtensionSettings 
@ptrval 
@BODYpos
4
@emailBODY

-- set the start position for the Parameter 1
SELECT @PARAM1Pos = patindex('%|P1|%', Parameters) - 1
FROM Subscriptions WHERE SubscriptionID = @subscriptionID

IF isnull(@PARAM1Pos, '') <> '' and @PARAM1Pos > 0 and len(@param1) > 0
-- change the Parameter 1 value
UPDATETEXT Subscriptions.Parameters 
@PARAMptrval 
@PARAM1Pos
4
@param1

-- run the job
exec msdb..sp_start_job @job_name = @scheduleID


-- this give the report server time to execute the job
WAITFOR DELAY '00:00:10'

-- set the start position for the TO Address
SELECT @TOpos = patindex('%' + @emailTO + '%', ExtensionSettings) - 1
FROM Subscriptions WHERE SubscriptionID = @subscriptionID

SELECT @length = len(@emailTO)

        IF @length > 0
-- replace the addresses with the original |TO|
UPDATETEXT Subscriptions.ExtensionSettings 
@ptrval 
@TOpos
@length
'|TO|'

-- set the start position for the TO Address
SELECT @CCpos = patindex('%' + @emailCC + '%', ExtensionSettings) - 1
FROM Subscriptions WHERE SubscriptionID = @subscriptionID

SELECT @length = len(@emailCC)

        IF @length > 0
-- replace the addresses with the original |CC|
UPDATETEXT Subscriptions.ExtensionSettings 
@ptrval 
@CCpos
@length
'|CC|'

-- set the start position for the TO Address
SELECT @BCCpos = patindex('%' + @emailBCC + '%', ExtensionSettings) - 1
FROM Subscriptions WHERE SubscriptionID = @subscriptionID

SELECT @length = len(@emailBCC)

        IF @length > 0
-- replace the addresses with the original |BC|
UPDATETEXT Subscriptions.ExtensionSettings 
@ptrval 
@BCCpos
@length
'|BC|'

-- set the start position for the REPLY TO Address
SELECT @RTpos = patindex('%' + @emailReplyTO + '%', ExtensionSettings) - 1
FROM Subscriptions WHERE SubscriptionID = @subscriptionID

SELECT @length = len(@emailReplyTO)

        IF @length > 0
-- replace the addresses with the original |RT|
UPDATETEXT Subscriptions.ExtensionSettings 
@ptrval 
@RTpos
@length
'|RT|'

-- set the start position for the BODY Text
SELECT @BODYpos = patindex('%' + @emailBODY + '%', ExtensionSettings) - 1
FROM Subscriptions WHERE SubscriptionID = @subscriptionID

SELECT @length = len(@emailBODY)

        IF @length > 0
-- replace the addresses with the original |RT|
UPDATETEXT Subscriptions.ExtensionSettings 
@ptrval 
@BODYpos
@length
'|BD|'

-- set the start position for the Parameter 
SELECT @PARAM1Pos = patindex('%' + @param1 + '%', Parameters) - 1
FROM Subscriptions WHERE SubscriptionID = @subscriptionID

SELECT @length = len(@param1)

        IF @length > 0
-- replace the addresses with the original |P1|
UPDATETEXT Subscriptions.Parameters 
@PARAMptrval 
@PARAM1Pos
@length
'|P1|'


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO