get storedproc return value in dts

Hi,

I have a stored proc which has an update statement. There are 3 cases, it either does the update(n rows), it doesn't do any update(0 rows) or it fails. If it fails I can use the fail task to send myself email and if it succeeds I can send email as well. However, if it doesn't run at all i.e. 0 rows updated then it's neither success nor failure. I would like to know how i can find out if it did not run or didnt do any changes so that I can send email accordingly.

Please advise.

Thanks

Devsql

Asked: 2006-07-24

Advertise Here

Databases

1 Solution

LVL 66

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented: 2006-07-24

Here's one way. I imagine this will vary greatly by developer.

CREATE PROCEDURE YourProc(@success bit) AS

UPDATE YourTable
SET Column1 = Value1

IF @@ROWCOUNT > 0
begin
-- Your Update statement updated at least one record.
@success =1
end
else
begin
-- Your Update statement did not return any records.
@success=0
end

SELECT @success
GO

LVL 75

Anthony PerkinsCommented: 2006-07-24

You cannot return an output parameter to DTS (at least not directly) You will have to resort to returning a resultset containing the variable, as suggested in the previous comment, make sure to alias it.

LVL 75

Anthony PerkinsCommented: 2006-07-24

Some small modifications to Jim's code (no points please):

CREATE PROCEDURE YourProc()

AS

SET NOCOUNT ON

UPDATE YourTable
SET Column1 = Value1
WHERE <YourConditionGoesHere>

Select @@ROWCOUNT as [RowCount]

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Expand your skills with this month's free Premium course.

get storedproc return value in dts

Join & Write a Comment Already a member? Login.

Featured Post