CODEWITHSUNDEEP
sqlms-accessstored-proceduresparameters
Kildareflare
Kildareflare

Reputation: 4768

SQL stored procedure where database to query is parameter

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.

Upvotes: 0

Views: 1120

Answers (2)

fearoffours
fearoffours

Reputation: 1475

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;

Upvotes: 0

Bruno Costa
Bruno Costa

Reputation: 2720

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

I think it is what you are looking for.

Upvotes: 1

Related Questions