I currently run pass-through queries with parameters against a SQL-server backend. In order to pass the parameter, I basically use vba to substitue the literal value for the parameter at runtime and this pass this string to SQL server for processing. The only problem is, this is done in a loop, passing thousands of literal predicates to the backend for processing, thus causing thousands of cached queries. In order to make this more efficient on the server, I need to make it so the same query stays in the cache (instead of thousands of individual queries with different literal predicates specified). In Oracle, I can do this by using a bind variable. Basically, I pass the query to Oracle referencing the bind variable, Oracle prompts my application for the value of the bind, which my app then provides.
Unfortunately, I don't know how to provide the bind variable a value in Oracle yet. Does anyone know how to do this in SQL Server? This is basically a workaround to sending a parameter query in a pass-through.