Getting Status Indicator back from an SP

[Guest_imported]

Hi
I need to write an SP as follows : -
Passing in will be values X & Y.
I have a 2-field table : Field 1 char
Field 2 date.
If X matches Field 1, use Y to update Field 2.
If X does not match, then insert a new record with X & Y.
If either of the above 2 successful, then return a 0.
If neither succesful,(ie Y not numeric) then return a 1.
i.e. select "err" = 'status'. ('status' = 0 or 1).
I have written straightforward Selects before within SP's, but nothing like this.
The OS is UNIX.
I know there is "@@error", and also the "return status", but I can't find the code to interrogate them.
Help !

 

[qyllr]

hello,

try this sample sp... you have to modify it according to your requirements...

----------------------------------------------
create proc
(
...
)
as
begin

--- declare variables ---
declare @status int,
@save_error int,
@X char,
@Y datetime

--- initialize status ---
select @status = 0

--- validate ---
if (@X = NULL)
begin
select @status = 1
raiserror 20001 &quot;<<ERROR>> X must be entered.&quot;
end

if (@Y = NULL)
begin
select @status = 1
raiserror 20001 &quot;<<ERROR>> Y must be entered.&quot;
end

--- on errors exit ---
if (@status = 1)
return

--- update/insert ---
begin tran

--- update @Y ---
if exists
(
select 1
from tableName1
where columnName1 = @X
)
begin
update tableName1
set columnName2 = @Y
where columnName1 = @X

select @save_error = @@error

if (@save_error != 0)
begin
raiserror 20001 &quot;<<ERROR>> Updating tableName1.&quot;
rollback transaction
return
end
end

--- insert X and Y ---
else
begin
insert into tableName1 (X, Y)
values (@X, @Y)

select @save_error = @@error

if (@save_error != 0)
begin
raiserror 20001 &quot;<<ERROR>> Inserting tableName1.&quot;
rollback transaction
return
end
end

commit tran

end
----------------------------------------------

hth,
q.

 

[grega]

If you want to save a value returned from a s/p you can save the return status in a variable. Say you have a procedure like this ..

create procedure my_proc as
return 99

In your calling SQL youcan say

declare @retstat int
exec @retstat = my_proc
select &quot;my_proc returned&quot;, @retstat

Greg.

 

[Guest_imported]

Hi qyllr

thanks for your suggestion.
When I pass a non-numeric date as Y, I get back
Msg 257, Level 16, State 1:
Procedure .....
Implicit conversion from VARCHAR to NUMERIC no allowed ...
(return status = -6)
I need to intercept this error, & return a meaningful message from the SP, which is being called externally.
I also need to recognise if there has been no access to the DB.
Regards