Return number of deleted records from SQL 2000

[rotelmak]

I am novice in sql2000. I call stored procedure with SQLEXEC function to delete invoice in sql2000 database. How can I see in VFP form number of deleted records ?

 

[bborissov]

I don't know HOW you call that SP, I suspect you use SQL Pass-Through. To get number of deleted records you could use this:
Put in your SP (SQL Server side) AFTER the DELET command:

....
DELETE FROM .... WHERE ...
SELECT @@ROWCOUNT AS DeletedRecs

Then in VFP Front end:

SQLEXEC(....,[call your SP here], [crsDeletedRecs])
SELECT crsDeletedRecs
BROWSE NORMAL

Keep in mind that I didn't add any error handling here.


Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[rotelmak]

Borislav,
crsDeletedRecs return zero, not number of deleted records

 

[bborissov]

Can you post the SP code?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[rotelmak]

Here is SP code:

CREATE PROCEDURE [dbo].del_invoice

@minvoice_no CHAR(12)

AS

begin

SET NOCOUNT OFF

BEGIN TRANSACTION

delete from mytable
WHERE
invoice_no = @minvoice_no


IF @@error <> 0
BEGIN
ROLLBACK TRANSACTION
RETURN
END


COMMIT TRANSACTION

SELECT @@ROWCOUNT AS DeletedRecs

 

[bborissov]

What you get if you change the SP to this:

[COLOR=blue]CREATE[/color] [COLOR=blue]PROCEDURE[/color] [dbo].del_invoice

    @minvoice_no [COLOR=blue]CHAR[/color](12)
        
[COLOR=blue]AS[/color]

[COLOR=blue]begin[/color]
[COLOR=blue]DECLARE[/color] @rCount [COLOR=blue]int[/color]
[COLOR=blue]SET[/color] [COLOR=#FF00FF]NOCOUNT[/color] [COLOR=blue]OFF[/color]

[COLOR=blue]BEGIN[/color] [COLOR=blue]TRANSACTION[/color]

[COLOR=blue]delete[/color] [COLOR=blue]from[/color] mytable
[COLOR=blue]WHERE[/color]
    invoice_no = @minvoice_no
[COLOR=blue]SET[/color] @rCount = @@ROWCOUNT

[COLOR=blue]IF[/color] @@error <> 0
     [COLOR=blue]BEGIN[/color]
       [COLOR=blue]SET[/color] @rCount = -1
       [COLOR=blue]ROLLBACK[/color] [COLOR=blue]TRANSACTION[/color]
     [COLOR=blue]END[/color]
[COLOR=blue]ELSE[/color]
   [COLOR=blue]COMMIT[/color]

[COLOR=blue]SELECT[/color] @rCount
[COLOR=blue]END[/color]

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[bborissov]

Also I have question, what type is invoice_no field?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[rotelmak]

Borislav,
invoice_no field is character type.

Now your code works fine.
Thank's