VB 6.0 - VB/SQL Server FAQ - Asked By Dhrubajyoti Dey on 09-May-01 02:33 AM

How can I pass a recordset from VB to a stored procedure of SQL Server? Help me. Is there any other way? Pls give me an example.

...pass an ADO object directly to a stored procedure in SQL Server.  It's not support.  Is there a specific task you are trying to do?

I want to send multiple records (each consists of multiple columns) to a stored procedure. Now if I call SP for each and every record obvoiusly the SP calling overhead will increase. Is there any mechanism to send all records in form of array to SP, so that in SP I will iterate via PATINDEX and make some update..in a process calling SP only once. Note: for each record I have to make some additional updates on other tables too.

Hmmm...I have seen stored procedures that handled a fixed number of nvarchar input parameters set to the max length allowed.  Say, maybe 10 or 20 total parameters.  In each of the input parameters, an entire Insert or Update string was stored.  

In the stored procedure, you could create a loop that captures the newly created id from the insert and process your updates.

Can you give me an example code for VB params passing and SQL Server iteration in SP, so that I can get you?

moADOCon and moADOCom are my ADO objects.  You may use your own naming convention. I kept the number of records to 2 for this example.

 dim moADOCon
 dim moADOCom
 dim lRecs

 Set moADOCon = Server.CreateObject("ADODB.Connection") 
 Set moADOCom = Server.CreateObject("ADODB.Command")
 moADOCon.CursorLocation = adUseClient
 moADOCon.open "connection string"

  With moADOCom
        .ActiveConnection = moADOCon
        .CommandText = "stored procedure name"
        .CommandType = adCmdStoredProc
        .Parameters.Append .CreateParameter("RC", adInteger, adParamReturnValue) 

        .Parameters.Append .CreateParameter("@string1", adVarChar, adParamInput, 400, sInsertString1)
        .Parameters.Append .CreateParameter("@string2", adVarChar, adParamInput, 400, sInsertString2)
                  
        .Execute lRecs, , adExecuteNoRecords
                
    End With

 Set moADOCom = nothing
 Set moADOCon = nothing

Here's a link to an article that has some sample code that you may need parts of including looping in a stored procedure and executing strings.

http://www.eggheadcafe.com/articles/20001002.asp

Now let me tell you the scenario. The whole thing I am going to apply in Master/Detail form, where this technique I am going to implement for Detail part. Now of course the no. of records in grid is not known to me (as specified by you as parameters). And it's not feasible to make a loop through my RS and adding 100s of parameters for SP. Isn't it? What can I do?

You may want to keep what we call here a "Dirty Flag".  Which means you don't attempt to save the record if it hasn't been changed.

I threw out the number "20" as an example.  You could do more or less.  As you iterate through the recordset, keep a counter.  When you reach a certain number which I'll refer to as X, call the stored procedure with the X number of  different parameters.  

If the counter is less than X at the end of the recordset, call the stored procedure again to get the remaining records inserted.

Iterating through a recordset in VB doesn't take all that long.  The time consuming portion is the transfer of data from ADO to SQL Server.  This methodology I mentioned above reduces the number of trips to the server.

Of course, you could always look into formatting XML and sending that to SQL Server.  I know SQL Server 2000 supports this but I personally haven't needed to take advantage of it yet.

Thanks Robbe for your excellent idea. This concludes our exchange of thoughts about this topic.