return value to VB from stored procedure

[MDA]

Hi,

I have a SP in SQL Server. The SP has a cursor that loops through records as it does a copy function. I would like to be able to return a value back to visual basic so I know how many records have been copied.

How do I return data to VB in a stored procedure while the SP is running? I am looking for the code required on the VB side and SQL side to communicate this data. Also any ideas on counting the records copied as it loops???

Thank in advance for any ideas or samples.

Regards,

Mike

 

[hellferret]

The SQL bit:

example:

CREATE PROCEDURE usp_Thing
@ret int output
AS
SELECT @ret=0

/*Do your looping cursor bit incrementing @ret by 1 every time*/





The VB bit: Set up an output parameter, before you execute.


Set prm = cmd.CreateParameter("Returned", adInteger, adParamOutput, 4, 0)
cmd.Parameters.Append prm


After your cmd.Execute your returned value will be held in cmd.Parameters(1)


Hope you can understand it, have to be quick - I'm at work.
HF

 

[MDA]

Thanks for your help HF,,, for some reason Query Analyzer does not like the @ret int output syntax. In particular the OUTPUT. You have me going in the right direction though...

Thanks

Mike

 

[hellferret]

You could cheat and just SELECT the count figure and then pick it up as a recordset in VB.
HF