Stored Procedure

[HLPCH]

Hi, I have couple of insert statements in the stored procedure. After i execute the stored procedure, how could i check if each one of them were successfull? Basically i want a flag inside the stored procedure which gives me the status of the updates.

Thanks

 

[jbenson001]

Have a select statement after the inserts to check the data.

 

[Ginka]

Or why don't you return the Identity value?
if it exists, your SP worked, else it didn't

 

[JohnDTampaBay]

If you have a procedure with multiple inserts, you check if each one is successful when SCOPE_IDENTITY() IS NOT NULL.

1 - Declare a variable to hold the flag (@flag tinyint)
2 - Initialize it (SET @Flag = 0)
3 - After the first insert check SCOPE_IDENTITY()
(IF SCOPE_IDENTITY() IS NOT NULL
SET @Flag = @Flag + 1)
4 - After the second insert check SCOPE_IDENTITY()
(IF SCOPE_IDENTITY() IS NOT NULL
SET @Flag = @Flag + 2)
5 - Finally at the end of the procedure, return the value of @Flag.

If @Flag = 0, neither succeeded. If @Flag = 1, only the first succeeded. If @Flag = 2, only the second succeeded. If @Flag = 3, both succeeded. Good luck!


--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[NoCoolHandle]

Assuming you don't have an idenity property and that stuff doesn't work..

SQL has a built in variable @@rowcount that returns the number of rows affected from the last sql statement.
(select,insert,update,delete)

The trick is that it is reset just by looking at it (reading it)

The Blue code would be stuff that will work , the red is flawed.

So inside you proc

create proc someexample
as
set nocount on
Insert into a values(1)
[blue]if @@rowcount = 0 
    begin
       print 'No Records were inserted
    end

Declare @r int [Green]-- Used for preserving ROWCOUNT value[/Green]
update someothertable set X = 'abc' where y = 'smomething'
set @r = @@ROWCOUNT
   if @r = 0 
     begin  

    end
   else
    begin
      print cast( @r  as varchar(2000)) + 'rows were added'
    end
[/blue]
[red]
update someothertable set X = 'abc' where y = 'smomething'
   if @@rowcount = 0 
     begin  
       print 'no records modified'
    end
   else
    begin
      print cast(@@rowcount as varchar(2000)) + 'rows were modified' [Green]--  this will always fail to be acccurate as @@rowcount now = the line where it was checked..[/green]
    end
[/red]

HTH

ROb

 

[HLPCH]

Great.Thanks for all the replies.