Stored Procedure Resultsets?

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: SMK (anonymous_at_discussions.microsoft.com)
Date: 03/22/04 

Date: Sun, 21 Mar 2004 16:26:07 -0800

Hello,

I am having a problem with the performance with when calling stored
procedures. I have to do a large number of inserts/updates to a table, so
speed is a big factor. Here is one of my simplified stored procedures I am
using for testing:

ALTER PROCEDURE dbo.I7PROCaddAnalog(@p_Analog_Id CHAR(20),
                              @p_Analog_Value REAL,
                              @p_Analog_Subs INTEGER) as

begin
  SET NOCOUNT ON

            UPDATE IRISAnalog SET
                  Analog_Value = @p_Analog_Value,
                  Analog_Subs = @p_Analog_Subs
                  WHERE Analog_Id = @p_Analog_Id

end

I am using the C code to call it:

rc = SQLAllocStmt(hdbc, hstmt);
rc = SQLPrepare(*hstmt, "{call AddAnalog(?,?,?)}", SQL_NTS);
rc = SQLNumParams (*hstmt, &numParams);
[BINDPARAMETERS CALL]

while(I am looping)
{
rc = SQLExecute(*hstmt);
rc = SQLFreeStmt(*hstmt, SQL_CLOSE); OR rc = SQLMoreResults(*hstmt);
}

It seems like I have to close the cursor before I execute another call. Is
it fair to say that this is returning a resultset? This seems to be very
slow.

I have also tried to use the following:
rc = SQLAllocStmt(hdbc, hstmt);
rc = SQLPrepare(*hstmt, "UPDATE IRISAnalog SET Analog_Value = ?,
Analog_Subs = ? WHERE Analog_Id = ?", SQL_NTS);
rc = SQLNumParams (*hstmt, &numParams);
[BINDPARAMETERS CALL]

while(I am looping)
{
rc = SQLExecute(*hstmt);
}

This example I do not have to close the cursor. And can quickly execute
another call.

Is there any way for the stored procedure to not return a resultset (if it
is returning one) or speed up the efficiency of the stored procedure call?

Thanks for your help,

Steve

Relevant Pages

  • Stored Procedure Resultset
    ... rc = SQLNumParams (*hstmt, &numParams); ... It seems like I have to close the cursor before I execute another call. ... Is there any way for the stored procedure to not return a resultset (if it ...
    (microsoft.public.sqlserver.odbc)
  • Re: Connection is busy with results for another hstmt
    ... Erland's lockinfo should help: ... > execute (SELECT sa_id, ... > Driver]Connection is busy with results for another hstmt ... > I wish to find out what the other hstmt is that's causing the problem. ...
    (microsoft.public.sqlserver.programming)
  • Connection is busy with results for another hstmt
    ... execute (SELECT sa_id, ... Driver]Connection is busy with results for another hstmt ... I wish to find out what the other hstmt is that's causing the problem. ... Garey ...
    (microsoft.public.sqlserver.programming)
  • Re: Stored Procedure Resultsets?
    ... > It seems like I have to close the cursor before I execute another call. ... > it fair to say that this is returning a resultset? ... > Is there any way for the stored procedure to not return a resultset (if it ...
    (microsoft.public.sqlserver.programming)