Output Parameters, Stored Proc and DTS

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Output Parameters, Stored Proc and DTS

Author

Topic

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-05-09 : 03:22:56

I have a DTS package, where in a step I call a SP. I need to get the rowcount of the select statements inside that SP.The following is the code of the SP, followed by the code used in Execute Sql Task in DTS.


SP Code


CREATE PROCEDURE [dbo].[usp_dts_tempusexport] @rc int output
AS


BEGIN
	BEGIN TRANSACTION



--TempTable - Select Statement - Zero Month Email
--______________________________________________________

select col1,col2,col3 from TempTable T1 


UNION ALL

--TempTable - Select Statement - 1.7 Template Email
--______________________________________________________


select col1,col2,col3 from CustomerDetail T1 

UNION ALL

--TempTable - Select Statement - 2.1 Three Month Email
--______________________________________________________________________

select col1,col2,col3 from TempTable T1 

UNION ALL

--TempTable - Select Statement - 3.1 Six Month Email
--______________________________________________________________________

select col1,col2,col3 from TempTable T1 


Set @rc = @@rowcount
Select @rc

Update CustomerPurchaseProduct Set TemplateMailId = '1.1' Where  TemplateMailId = '-1'
Update CustomerPurchaseService Set TemplateMailId = '2.1' Where  DateDiff(month,orderdate,getdate()) = 3 and Cast(TemplateMailId as float) < 2.1
Update CustomerPurchaseService Set TemplateMailId = '3.1' Where  DateDiff(month,orderdate,getdate()) = 6 and Cast(TemplateMailId as float) < 3.1
Update CustomerPurchaseService Set TemplateMailId = '4.1' Where DateDiff(month,getdate(),ServiceEndDate) <= 3 and Cast(TemplateMailId as float) < 4.1


COMMIT TRANSACTION
END
GO

Execute SQL Task Code:
----------------------

Exec usp_dts_importtempdata

DECLARE @RC int
EXEC @rc=usp_dts_tempusexport 
SELECT @RC AS ReturnValue

When I click on Parameters, then I get a message No parameters for the sql statement.

What am I doing wrong here?

Karunakaran
___________
It's better to be loved and lost, than ever to be loved...

SreenivasBora
Posting Yak Master

164 Posts

Posted - 2005-05-09 : 16:50:42

Hey Karuna,

Please change the code as below....

DECLARE @RC int
EXEC usp_dts_tempusexport @rc OUTPUT
SELECT @RC AS ReturnValue

Hope this will work for you :-)

With Regards
Sreenivas Reddy B

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources