how to return success from a stored procedure

[DougP]

given this simmpel procedure, I want to know that it updated the record or not to pass something back to my ASP.net WEB page.
Waht is an yweasy way to do this? check for something? is there Success flag triggered if it was good?

ALTER Procedure [dbo].[sp_SOWUpdateTimeReporting]
	@RecordID int,
    --'@ResourceLastName	nvarchar(50),
	--@ResourceFirstName	nvarchar(50),
	--@Vendor	nvarchar(20),
	--@ResourceType	nvarchar(20),
	--@OffshoreOnShore	nvarchar(20),
	@SPMID	nvarchar(20),
	@CostTracker	nvarchar(20),

	@Year	int	,
	@Month	int	,
	@Day	int	,
	
	@ADActivityCode	nvarchar(20),
	@HoursWorked	float
	--@ApprovedBy	nvarchar(30),
	--@DateWorked	datetime,
	--@SOWTracker	nvarchar(20),
	--@WeekEndDate	datetime
AS 

	Update  SOWTimeReporting
		set
	SPMID = @SPMID,	
		CostTracker = @CostTracker,	
		[Year] = @Year	,	
		[Month]	 = @Month,	
		[Day] = @Day,
		ADActivityCode = @ADActivityCode,	
		HoursWorked = @HoursWorked
		Where SOWTimeReportingUniqueID = @RecordID

DougP

 

[bborissov]

If something goes wrong here you will have an exception in your frontend.

Borislav Borissov
VFP9 SP2, SQL Server

 

[DougP]

Thats what I want? to know?

DougP

 

[DougP]

so your saying if it fails I get an erro if it succeds I get notihng? can I make a procdure check somehow and retunr "good" update? I am making this. but is there a better way?

	Set @CheckSPMID = (Select SPMID from SOWTimeReporting 
	Where SOWTimeReportingUniqueID = @RecordID)
	If @CheckSPMID = @SPMID
		set @Result = 1
	else
		set @Result = 2
	
	
	Select Result as @Result

DougP

 

[imex]

Hi,

I believe you can check the value of @@ROWCOUNT:

http://msdn.microsoft.com/en-us/library/ms187316.aspx

Hope this helps.


[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imoveis-venda-são_paulo-residencial-apartamento[/url]

 

[bborissov]

Use TRY and CATCH, maybe in both sides.
Check TRY and CATCH in BOL.
You could use OUTPUT parameter to return value from SP. Or just return some integer value that shows you the result. 0 - success; >0 - error.

Borislav Borissov
VFP9 SP2, SQL Server

 

[Qik3Coder]

Doug, If you mix everyone's answers:

--Your sql code
IF @@Rowcount = 1
begin
 select (Cast 1 as bit) Result
end
else
begin
 select (cast 0 as bit) Result  
end

If you're using linq, you will be able to directly access the result like this:

sp_SOWUpdateTimeReportingResult r = dbContext.sp_SOWUpdateTimeReportingResult(myIDVariable);
return r.Result;

You've got questions and source code. We want both!
There's a whole lot of Irish in that one.