records returned by stored procedure

[shultz]

Hi,

I have a stored procedure which accepts a start datetime and end datetime as parameters and picks up records from a view and inserts those into another table. And I'm calling this from my VB program. No problem in execution. So far so good. How can I know, how many rows have been inserted by the procedure.
Here is my stored procedure:

CREATE PROCEDURE [SP_HISTORYABNORMALCELLSTATUS] (@insFromDateTime decimal, @insToDateTime decimal) AS

INSERT INTO TBLABNORMALCELLSTATUS(PCUSNID, PCUSA, CONN, PCMLK, P_BVC, PB_STATE, PC_STATE,
P_BUCKETSIZE, P_LEAKRATE, CELLID, PB_INSDATETIME, PC_INSDATETIME, SGSNID, SGGTL, NSEI,
S_BVC, S_STATE, S_BUCKETSIZE, S_LEAKRATE, S_INSDATETIME, BSC, CELLNAME, LOCATIONAREACODE)

SELECT PCUSNID, PCUSA, CONN, PCMLK, P_BVC, PB_STATE, PC_STATE, P_BUCKETSIZE, P_LEAKRATE,
CELLID, PB_INSDATETIME, PC_INSDATETIME, SGSNID, SGGTL, NSEI, S_BVC, S_STATE, S_BUCKETSIZE,
S_LEAKRATE, S_INSDATETIME, BSC, CELLNAME, LOCATIONAREACODE FROM VEWABNORMALCELLSTATUS WHERE
(PB_INSDATETIME BETWEEN @insFromDateTime AND @insToDateTime AND S_INSDATETIME IS NULL)
OR
(PB_INSDATETIME IS NULL AND S_INSDATETIME BETWEEN @insFromDateTime AND @insToDateTime)
OR
(PB_INSDATETIME BETWEEN @insFromDateTime AND @insToDateTime AND
S_INSDATETIME BETWEEN @insFromDateTime AND @insToDateTime)

RETURN @@ROWCOUNT

And here is my VB Code to execute the above:

tmpProcText = "EXEC SP_HISTORYABNORMALCELLSTATUS " & insFromDateTime & ", " & insToDateTime

conn.Execute (tmpProcText)

anybody can give me some idea(s)??

Thanks a lot in advance..!!

 

[sunaj]

Hi,

there is 2 options:

1) Change your stored procedure to use an OUPUT parameter and use the command object from VB to pick up the value. Fastest, recommended.

2) SELECT the @@rowcount in your stored procedure. Run your Sored Procedure from VB by opening a recordset. The values will be return as a recordset (1 row, 1 col).
Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[shultz]

Hi,

I modified the stored procedure and the VB code like this. But I think there is some problem in executing the Command object. I'm sure that there is a different way of executing it. Can anybody help me.


CREATE PROCEDURE [SP_HISTORYABNORMALCELLSTATUS] (@insFromDateTime decimal, @insToDateTime decimal,
@recsInserted int OUTPUT) AS

INSERT INTO TBLABNORMALCELLSTATUS(PCUSNID, PCUSA, CONN, PCMLK, P_BVC, PB_STATE, PC_STATE,
P_BUCKETSIZE, P_LEAKRATE, CELLID, PB_INSDATETIME, PC_INSDATETIME, SGSNID, SGGTL, NSEI,
S_BVC, S_STATE, S_BUCKETSIZE, S_LEAKRATE, S_INSDATETIME, BSC, CELLNAME, LOCATIONAREACODE)

SELECT PCUSNID, PCUSA, CONN, PCMLK, P_BVC, PB_STATE, PC_STATE, P_BUCKETSIZE, P_LEAKRATE,
CELLID, PB_INSDATETIME, PC_INSDATETIME, SGSNID, SGGTL, NSEI, S_BVC, S_STATE, S_BUCKETSIZE,
S_LEAKRATE, S_INSDATETIME, BSC, CELLNAME, LOCATIONAREACODE FROM VEWABNORMALCELLSTATUS WHERE
(PB_INSDATETIME BETWEEN @insFromDateTime AND @insToDateTime AND S_INSDATETIME IS NULL)
OR
(PB_INSDATETIME IS NULL AND S_INSDATETIME BETWEEN @insFromDateTime AND @insToDateTime)
OR
(PB_INSDATETIME BETWEEN @insFromDateTime AND @insToDateTime AND
S_INSDATETIME BETWEEN @insFromDateTime AND @insToDateTime)

SELECT @recsInserted = @@rowcount

*********************************************

Dim cmd As New ADODB.Command
Dim param1 As New ADODB.Parameter
Dim param2 As New ADODB.Parameter
Dim recsReturned As Long

With cmd
.ActiveConnection = conn
.CommandText = "SP_HISTORYABNORMALCELLSTATUS"
.CommandType = adCmdStoredProc
End With

param1.Direction = adParamInput
param1.Type = adDouble
param1.Value = insFromDateTime

param2.Direction = adParamInput
param2.Type = adDouble
param2.Value = insToDateTime

cmd.Parameters.Append param1
cmd.Parameters.Append param2

updateLogs ("About to insert recs in TBLABNORMALCELLSTATUS " & Now())

cmd.Execute recsReturned

The problem lies in the above line. How can I re-write this code to capture the OUTPUT value from the stored procedure.

Thanks a lot..!!

 

[sunaj]

You need to append an oupput parameter:

--------------------
cmd.parameters.Append cmd.CreateParameter("@recsInserted", adInteger, adParamOutput)
--------------------

(or with a adodb.parmeter like you did with the input parameters)

Then simply:
--------------------
'.....
cmd.Execute recsReturned
Msgbox Cstr(Cmd("@recsInserted&quot) & " records inserted."
--------------------

Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'