Passing Paramater to Stored Proc that run in DTS -- sql server dts

mk
12/31/2003 12:36:51 AM

I have the stored procedure where i am accepting the date
as input parameter for the previous month and current
month. The stored Procedure is as follows :

CREATE proc spDataTransfer_Monthly(@PrevStartDate char
(20),@PrevEndDate char (20),@CurrStartDate char
(20),@CurrEndDate char

(20),@Prevtblpostfix char (8),@Currtblpostfix char (8))
AS
BEGIN
declare @sqlstr varchar(5000)
SET NOCOUNT ON

--============= DATA TRANSFER ================

--Previous Month Table

select @sqlstr = 'INSERT INTO dbo.SHIFT'+ rtrim
(@Prevtblpostfix) +'
SELECT DISTINCT T2.*
FROM dbo.[SHIFT] T2 left JOIN
dbo.SHIFT'+ rtrim(@Prevtblpostfix) +' T1 ON
T1.SHIFT_DTG = T2.SHIFT_DTG AND
T1.MACHINE_NUMBER = T2.MACHINE_NUMBER AND
T1.SHIFT_ID = T2.SHIFT_ID
WHERE T1.SHIFT_INDEX IS NULL
AND T2.shift_dtg>='''+@PrevStartDate+''' and
T2.shift_dtg<='''+@PrevEndDate+'''

--Next Month Table

select @sqlstr = 'INSERT INTO dbo.SHIFT'+ rtrim
(@Currtblpostfix) +'
SELECT DISTINCT T2.*
FROM dbo.[SHIFT] T2 left JOIN
dbo.SHIFT'+ rtrim(@Currtblpostfix) +' T1 ON
T1.SHIFT_DTG = T2.SHIFT_DTG AND
T1.MACHINE_NUMBER = T2.MACHINE_NUMBER AND
T1.SHIFT_ID = T2.SHIFT_ID
WHERE T1.SHIFT_INDEX IS NULL
AND T2.shift_dtg >= '''+@CurrStartDate+''' and
T2.shift_dtg <= '''+@CurrEndDate+'''

SET NOCOUNT OFF

END
GO

--To Execute Stored Proc.
exec
spDataTransfer_Monthly '01/01/2003','01/31/2003','02/01/200
3','02/28/2003','_2003_1','_2003_2'

How can i pass the date value to the above stored
procedure dynamically since this stored procedure is
running in through DTS on Daily basis.

Allan Mitchell
12/31/2003 8:49:38 AM

Have you read this article?

Global Variables and Stored Procedure Parameters
(http://www.sqldts.com/Default.aspx?234)

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

[quoted text, click to view]

sql server dts : Passing Paramater to Stored Proc that run in DTS