Store Procedure

[sonya9879]

Hi, I am trying to have a procedure that it checks if some data exist on one table and if it does then it perfoms an update in another table and if it doesn't then does nothing. I have the following procedure, although it doesn't actually work yet don't know why , this shows my idea. Is this the right way of doign this?


CREATE procedure ChangeStatus
(
@pID int,
@cID int,
@lID int,
@sNME varchar(50) OUTPUT,
@sADD varchar(100) OUTPUT
)
as
select
@sNME=sNME,
@sADD=sADD
from
table1
where
(pID = @pID)
begin
set XACT_ABORT ON
DECLARE @EXIST int
begin transaction
set nocount on
set @EXIST = (select b_sNME FROM table2 WHERE (UPPER(b_sNME) = @sNME) AND (b_cID= @cID))
if (@EXIST) begin
update
table3
set
fStatus=2
where
uID=@lID
end
commit transaction
end
GO

 

[sonya9879]

i have the query working, I just corrected the IF statement that the syntax was wrong:

if (@EXIST) begin = if (@EXIST IS NOT NULL) begin


In any case, I am calling this procedure from another procedure and I would like to know if this is the best way to do it, or in other words, is this procedure tunned properly? thanks to whoever is into procedures

 

[camaleonCHILE]

Sonya: calling a SP into another SP is a good way to perform a task, so you are rigth. In this example in particular, you should try the next code in the "if exists" part:

IF EXISTS(select b_sNME FROM table2 WHERE (UPPER(b_sNME) = @sNME) AND (b_cID= @cID))
BEGIN
UPDATE table3
SET fStatus=2
WHERE uID=@lID
END

Regards,cmn

 

[sonya9879]

hi cmn, thanks for the observation, i have made the changes as you suggested using EXISTS. thanks again for the tip