Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 267
  • Last Modified:

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
0
Devsql
Asked:
Devsql
  • 2
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
0
 
Anthony PerkinsCommented:
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.
0
 
Anthony PerkinsCommented:
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]
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now