SQL stored procedure where database to query is parameter

Question

I am working with Microsoft Access (not SQL server) and have written a stored procedure (Query) as shown below in SQL.

This procedure works but it relies on a hard coded path to the database from which to pull (SELECT) the data.

To make the query more useful I want to pass the database to SELECT from as a parameter to the query - how does one do this?

INSERT INTO Part_Batteries ( ItemCode, Size, Voltage )
SELECT tblBatteries.ItemCode, tblBatteries.SizeAH, tblBatteries.Voltage
FROM tblBatteries IN 'C:\Databases\DeviceDatabases\UKDevices.mdb';

I.e. I want to replace the line

FROM tblBatteries IN 'C:\Databases\DeviceDatabases\UKDevices.mdb';

with something like this

FROM tblBatteries IN @DB

Currently I am testing the procedures by either clicking on them in Access or by calling them from a VB module.

When I am satisfied they work I will call them as required from the main application that will be built using C#.

Thanks in advance.

Answer 1

Hi,

Please read this http://www.sommarskog.se/dynamic_sql.html#storedprocedures

I think it is what you are looking for.

Answer 2

You need to declare the variable at the top of your stored proc

DECLARE @DB AS string

INSERT INTO Part_Batteries ( ItemCode, Size, Voltage )
SELECT tblBatteries.ItemCode, tblBatteries.SizeAH, tblBatteries.Voltage
FROM tblBatteries IN @DB;