Dynamic SQl

[JHausmann]

A stored procedure is pre-compiled (the query plan is generated when you run it the first time). If you change the conditional portion of your stored procedure, it will compile it again. In short, a "dynamic stored procedure" would be no better than a pass-thru query (with some exceptions, notably permissions, triggers and remote execution from a local server)...

What it sounds like you need to do is create a passthru query (if the where condition changes) or only pass in the variables that change (if your stored prcedure always searches for lastname and firstname, then you want to make the variables that represent lastname and firstname, parameters).

[Platypus1]

To JHausmann:

I understand what you said about stored procedures: I would like to use a stored procedure in the case where the parameter is the same ie looking up the primary key; but would use a SQL Statement in an ad hoc manner if the user needs multiple criteria or needs something like:

DateOpened >= '2000-05-30' , etc.

Can you give an exaample of what you call a passthru query?

The problem is passing a string as just the where clause; he DB does not recognize the right side of the equals sign as a value string, but is trying to find a column name instead. My string must be wrong. Any ideas?

Thanks.

[JHausmann]

Try:

Dim g_Db As New ADODB.Connection
Dim rs As New ADODB.Recordset
dim sTmp as string
dim sUserParms as string

'open your connection here
sUserParms=" LastName = 'Smith' AND FirstName = 'Susan' "

sTmp="SELECT FirstName,LastName,Phone,Problem FROM Workorders WHERE " & sUserParms

rs.Open sTmp, g_Db

If rs.EOF = False Then
Do While Not rs.EOF
'do something with the returned data here
rs.movenext
loop
end if
rs.Close


Set rs = Nothing