August 19, 2004 at 12:04 pm
Hi there, I am not familiar with sp and would appreciate some help. I would like to have an simple example of a stored proc that would do an update and would return 1 or 0 depending if the update was successful or not.
Thanks
August 19, 2004 at 2:15 pm
First, I know of no way to access the return value of a stored procedure outside of T-SQL such as in the form of an error code or the like unless you actually raise an error from T-SQL based upon the trapped return value.
CREATE PROCEDURE TestProc
@InputParm varchar(15)
AS
USE Northwind
set nocount on
declare @rc int
set @rc = 0
UPDATE Categories
SET Description = CONVERT(varchar, Description ) + 'Hello'
WHERE CategoryName = @InputParm
if @@ROWCOUNT = 0 set @rc = 1
if @@ERROR <> 0 set @rc = 1
return @rc
You would then use it like:
declare @rc int
EXEC @rc = TestProc @InputParm='Hello'
if @@ERROR <> 0 set @rc = 1 --In case TestProc hit a level 16 error
if @rc = 0 print 'Worked'
if @rc = 1 print 'Failed'
August 20, 2004 at 7:45 am
You can access return values from SPs if you are using a parameterized Command object in VB6/ADO - just add a parameter with the type adReturnValue. There's also a way to do this in PowerBuilder. So I guess other development languages that are geared up for working with SQL Server should have similar functionality.
HTH.
Charlotte.
August 20, 2004 at 7:52 am
Oh, and another thing:
in the example above, this bit of code...
UPDATE Categories
SET Description = CONVERT(varchar, Description ) + 'Hello'
WHERE CategoryName = @InputParm
if @@ROWCOUNT = 0 set @rc = 1
if @@ERROR <> 0 set @rc = 1
might not do what you expect. The value of @@ERROR is set after every single statement, not just update/insert/delete/select statements, so you have to get at it straight away. Here, the value of @@ERROR will be reset by the line 'if @@ROWCOUNT = 0 set @rc = 1' - I am guessing this is not what you want. I would do the following;
DECLARE @Err numeric
DECLARE @RowCount numeric
UPDATE Categories
SET Description = CONVERT(varchar, Description ) + 'Hello'
WHERE CategoryName = @InputParm
--Note that I am getting both the variables in the same statement.
SELECT @Err = @@ERROR, @RowCount = @@ROWCOUNT
if @RowCount = 0 set @rc = 1
if @@Err <> 0 set @rc = 1
cheers.
Charlotte
August 20, 2004 at 8:48 am
Thanks for the info and the catch, Charlotte. I apparently didn't think through that @@ERROR part enough. The adReturnValue thing might be usefull for me to know in the future.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply