Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2003
    Posts
    167

    Question Unanswered: 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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  4. #4
    Join Date
    Nov 2003
    Posts
    167
    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.

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Passing Parameters via VBA

    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

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If you are looking to get automatic parameterization, that VBA code is conceptually good.

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •