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

BIDS Variable Window Disappeared

I am experiencing a weird problem with SSIS development in BIDS...
When I click "View --> Other Windows --> Variables", the window doesn't show
up.
Has anyone else had this issue? If so did you solve it, and how?
Thanks!Hello Dan,
The actual window itself or the ability to edit variables?
Are you sure the window isn't already open just tabbed with the toolbox or
something like that?
What build are you running?
Allan Mitchell
http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com |
http://www.konesans.com

> I am experiencing a weird problem with SSIS development in BIDS...
> When I click "View --> Other Windows --> Variables", the window
> doesn't show up.
> Has anyone else had this issue? If so did you solve it, and how?
> Thanks!
>|||Allan,
Thanks for the reply.
The window itself doesn't show up. I have the window in auto-hide on the
left (with the toolbox on the left). When I traverse the menu (as stated) a
blank box appears as if it is trying to show me what I've asked.
I can get to (and edit) the variables by using the package explorer tab -
just not from the variables window.
Here are the builds of the different products I'm using:
-Microsoft Visual Studio Team Edition for Database Professionals Version
2.0.50727.251
-Microsoft Visual Studio 2005 Team Explorer
Version 8.0.50727.762
-Microsoft SQL Server Integration Services Designer
Version 9.00.3042.00
Thanks again for the help...
"Allan Mitchell" wrote:

> Hello Dan,
> The actual window itself or the ability to edit variables?
> Are you sure the window isn't already open just tabbed with the toolbox or
> something like that?
> What build are you running?
>
> --
> Allan Mitchell
> http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com |
> http://www.konesans.com
>
>
>

BIDS Variable Window Disappeared

I am experiencing a weird problem with SSIS development in BIDS...
When I click "View --> Other Windows --> Variables", the window doesn't show
up.
Has anyone else had this issue? If so did you solve it, and how?
Thanks!
Hello Dan,
The actual window itself or the ability to edit variables?
Are you sure the window isn't already open just tabbed with the toolbox or
something like that?
What build are you running?

Allan Mitchell
http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com |
http://www.konesans.com

> I am experiencing a weird problem with SSIS development in BIDS...
> When I click "View --> Other Windows --> Variables", the window
> doesn't show up.
> Has anyone else had this issue? If so did you solve it, and how?
> Thanks!
>
|||Allan,
Thanks for the reply.
The window itself doesn't show up. I have the window in auto-hide on the
left (with the toolbox on the left). When I traverse the menu (as stated) a
blank box appears as if it is trying to show me what I've asked.
I can get to (and edit) the variables by using the package explorer tab -
just not from the variables window.
Here are the builds of the different products I'm using:
-Microsoft Visual Studio Team Edition for Database Professionals Version
2.0.50727.251
-Microsoft Visual Studio 2005 Team Explorer
Version 8.0.50727.762
-Microsoft SQL Server Integration Services Designer
Version 9.00.3042.00
Thanks again for the help...
"Allan Mitchell" wrote:

> Hello Dan,
> The actual window itself or the ability to edit variables?
> Are you sure the window isn't already open just tabbed with the toolbox or
> something like that?
> What build are you running?
>
> --
> Allan Mitchell
> http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com |
> http://www.konesans.com
>
>