Multiple Stored Procedures

[jkmcgrath]

Hi,

The stored procedures used in my code is mounting, now I find myself having to create mulitiple recordsets and command objects. Some of these require parameters and a couple do not.

EXECUTE sp_xxxxx for each stored procedure wrapped up in a single stored procedure is what I was looking at so that I could change each sproc as needed and use it in other places in my code.

The question is how would I go about passing parameters to those sprocs wrapped up in the sproc or is there a better way?

This is with vb6 w/sp6 and MSSQL 2k w/sp4.

Thanks
John

[szlamany]

BTW - you should not name your SPROCS with sp_ as the prefix, as that causes the SQL ENGINE to see if a system sproc with name exists in the MASTER database.

We use this stored procedure:

CREATE PROCEDURE GetSqlSP
	 @StoredProc varchar(50)
AS
Select Parameter_Name,Data_Type,Parameter_Mode,Character_Maximum_Length
From INFORMATION_SCHEMA.Parameters
Where SPECIFIC_NAME = @StoredProc

To tell us what parameters, data_types, lengths exist for a given stored procedure.

We have a general routine for calling all our stored procedures that first calls this SPROC to see what is needed to call the stored procedure in question.

We then have a general "bind" routine that binds the VB variables/controls/arrays - whatever - to the parameters in the stored procedure.

Also - are you aware that a stored procedure can return many, many recordsets?

After 4 years of VB/MS SQL development we are up to around 1000 or so stored procedures for our three major business applications.

Never once in our VB app do we call these SPROCS specifically - we always use the methods described above.

[jkmcgrath]

Thanks szlamany I will rename those.

Multiple select statements in a single sproc if that is what you are referring to?

Here is the code in procedure that pulls the sproc..

'rs5******************************************************
    Dim strDodaac As String
    strDodaac = Trim(rs("dodac"))
    Set objCMD_AD.ActiveConnection = oCon
    objCMD_AD.CommandText = "sp_addr"
    objCMD_AD.CommandType = adCmdStoredProc
    objCMD_AD.Parameters.Append objCMD_AD.CreateParameter("@dodaac", adChar, adParamInput, 6, strDodaac)
    Set rs5 = objCMD_AD.Execute
'rs6******************************************************
    Dim strFrom As String
    strFrom = Trim(rs3("DODAAC"))
    Set objCMD_AD2.ActiveConnection = oCon
    objCMD_AD2.CommandText = "sp_addr"
    objCMD_AD2.CommandType = adCmdStoredProc
    objCMD_AD2.Parameters.Append objCMD_AD2.CreateParameter("@dodaac", adChar, adParamInput, 6, strFrom)
    Set rs6 = objCMD_AD2.Execute

And here is where I put the declarations in a module with a sub to open the db..

'MSSQL 2000 Connection
Public oCon As New ADODB.Connection
Public rs As New ADODB.Recordset
Public rs3 As New ADODB.Recordset
Public rs4 As New ADODB.Recordset
Public rs5 As New ADODB.Recordset
Public rs6 As New ADODB.Recordset
Public rs7 As New ADODB.Recordset
Public rs8 As New ADODB.Recordset
Public rsIN As New ADODB.Recordset
Public objCMD As New ADODB.Command
Public objCMDIN As New ADODB.Command
Public objCMD_SP As New ADODB.Command
Public objCMD_AD As New ADODB.Command
Public objCMD_AD2 As New ADODB.Command
Public objParm As New ADODB.Parameter
Public strSQL As String
Public strCON As String

To me it doesnt seem as though I am doing the best way or most effient way although it works

Just hate doing things sub par.

[szlamany]

Do you need rs5 and rs6 at the same time?

Where to you put the data from the recordsets?

We usually load them into flexgrids and immediately destroy the recordset.

I don't see a problem with calling a SPROC with a different "lookup key" many, many times.

We do use a single routine to call our SPROC though - so we simply pass the "lookup key" to the routine and it calls the SPROC.

[jkmcgrath]

Yes I need all the information at one time, What I have to do (or should I say the only I have been able to make it work the best so far) is have a form that has an "form" on it. To stack text on the form in the space provided i.e...
2
3
I have to use print x = y = during the loading of the form, During design phase I draw lines with the lines and provide labels for the rest of the text. I have a module containing a sub procedure that populates the labels on the forms.

So when I click "print" or "preview" the form is completed with all required data. (I am having to pull info from non RDBMS sources as well to complete this form)

Inline SQL statements were mounting so I wanted to reduce overhead (all this is coming from the network involving 3 servers the users workstation and then a networked printer, sFTP) and move all Inline statements to sprocs.

So I am at the point I still have the same number of recordsets but have equal number of command objects now which in my mind is creating more overhead for the creation of them. So I was trying to reduce the overhead of that process. There is a trip to the server for each one where I would like to reduce it to one and let the server process it once and send it once.

The sFTP I cannot trim until my request for a dll from weonlydo is approved, the search of the non-RDBMS cannot be trimmed until I can devise a way to import 4gigs of data from a dvd into the MSSQL 2k server.

So while some of the gains is small from the sprocs, every little bit is important for me at this point and the only areas I can improve is what I directly have control over and that is the sprocs, vb program and sql server.

Not sure my ramblings make any sense

[szlamany]

See post #10 in this thread:

http://www.vbforums.com/showthread.p...=nextrecordset

Actually, search for NEXTRECORDSET on the forum as well.

SPROCS can return many recordsets - but they can only be processed on the client one at a time.

[jkmcgrath]

Roger that but these recordsets are from serveral different sprocs containing unique info.

I would roll them up into one proc but each proc requires a parameter from the previous proc so guess that is where I have to find a solution is passing a parameter from my code to the first proc and then pass that parameter to the rest of the procs.

[kaffenils]

Roger that but these recordsets are from serveral different sprocs containing unique info.

I would roll them up into one proc but each proc requires a parameter from the previous proc so guess that is where I have to find a solution is passing a parameter from my code to the first proc and then pass that parameter to the rest of the procs.

Is this what you mean by passing the parameter to the rest of the sprocs?

create procedure MasterSproc @SomeParameter int
as
exec SubSproc1 @SomeParameter
exec SubSproc2 @SomeParameter