How to Pass varying parameters Via ADO into SQL7 Stored Procedure,

[Winla]

This is a question I am sure some of you would have experiences and answers. please help.

I am trying to develop a VB search interface for a SQL7 database. The problem is that users may have one to many fields to search. What is the effecient way (in coding and running) to achieve it???

The idea way of course is to pass a 2D array through ADO into a Stored Procedure and hopefully there is a way to handle array parameters in the procedure (in T-SQL language).

But I am not sure at all I can do that. My goal is to do all selection Hard-Coding in Stored Procedure. Can someone please give me some advices? Thanks a lot.

[Clunietp]

I can't find any way in T-SQL for SQL 7 to accept parameter arrays (similar to VBs paramarray)

What I have done with multiple options is build the query in code, adding a AND/OR CONDITION to the end of the SQL statement for every condition I need to meet. This works out fine.


HTH

Tom

[lychew]

Another way would be to pass in all the search fields into the store procedure thru parameters. May be you want to try this??

Dim objConnect As ADODB.Connection
Dim objCommand As ADODB.Command
Dim objParam As ADODB.Parameter
Dim rsForum As ADODB.Recordset
Dim strSQL As String

Set objConnect = New ADODB.Connection
objConnect.ConnectionString = DB_CONNECT
objConnect.Open

Set objCommand = New ADODB.Command
Set objCommand.ActiveConnection = objConnect
objCommand.CommandType = adCmdStoredProc

LoadParameters objCommand, mudtProps
objCommand.CommandText = "SP_SaveCategory"
objCommand.Execute

Private Sub LoadParameters(objCommand As ADODB.Command, mudtProps As CategoryProps)

Dim objParam As ADODB.Parameter

With mudtProps
Set objParam = objCommand.CreateParameter("LastVisited", _
adDate, adParamInput, , .LastVisited)
objCommand.Parameters.Append objParam
Set objParam = objCommand.CreateParameter("BMCID", _
adInteger, adParamInput, , .BMCID)
objCommand.Parameters.Append objParam
End With
Set objParam = Nothing

End Sub

mudtprops is a self create object which will store all the search criteria.

[Winla]

Thank you very much for reply. The way I am choosing is as follow:

in VB code,

item1= "item1" 'search item value
item2="item2"
item3=""
item4="item4"
......
folowed by ADO connection and procedure calling

In Stored procedure

for all input variables, assign default value to '' and then using AND condition in the query.

This has solved my problem although the stored procedure is not flexible enough to accept run-time assigned fields search.

Thanks again