bind variables / parameter queries

Hi,

I'm writing an Access pass-through query against a SQL server backend and I need some advice on passing parameters. Currently I use vba to substitute the literal values for the parameters prior to passing the query to SQL Server. However, I am going through a loop thousands of times with different literals for these parameters which causes the server's cache to fill up. In Oracle, there is a way to use bind variables for the parameters so that only one copy of the query is cached.

Does anyone know how I can do this in SQL Server?

For instance, I have 20,000 employees and I'm pulling info by SS#:

Select * from EmpTable where SS_number = [SSN]

Is there a way I can pass this query to SQL Server and then pass the value of [SSN] as I loop through the dataset?

Thanks.write a stored procedure, and instead of calling the database engine 20,000 times, just call it once and pass it a list of 20,000 numbers

come to think of it, where would you get 20,000 numbers? sounds like you might want to look for a JOIN solution|||SQL Server actually goes you one better, in that its ODBC drivers will automagically parameterize a query for you (unless you get really creative in modifying the query).

As Rudy pointed out though, if you have more than 20 iterations from a given client, you really ought to be thinking about a JOIN based solution... Doing that kind of thing on that scale one row at a time is WAY too much work for me!

-PatP|||Thanks, guys. I'll get write access to the backend and write a stored proc.

I'll have to read up on how to pass values to the proc (I'm guessing it's like a function).

Thanks again.|||How about this?:

Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection
cnn.Open "DSN=PKRebate2001", "sa", ""
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = cnn
.CommandText = "sp_UpdateCustomerUnique"
.CommandType = adCmdStoredProc
.Parameters.Refresh
.Parameters("@.ImportMonth").Value = IM
.Execute
End With
Set cmd = Nothing
Set cnn = Nothing|||If you are looking to get automatic parameterization, that VBA code is conceptually good.

-PatP