Input parameters

[airedale9]

I'm running a simple insert/select query that requires a change in table names each time it is run. I want to set it up as a stored procedure using input parameters for the two table names that change, but am not sure if SQL accepts paramaters in place of table names. The code so far is:

create procedure sp_DataProv
as
insert into tblDataProvPartNum
(
mc,
nsc,
nc,
iin,
partnum,
cttcode,
smb,
smbsub
)
select
a.mc,
a.nsc,
a.nc,
a.iin,
a.partnum,
a.cttcode,
b.smb,
b.smbsub
from tblPartNum a left join tblCostUse b on
a.mc=b.mc and a.nc=b.nc and a.iin=b.iin

Any help on how to replace the 2 table names in the select statement would be much appreciated.

 

[jackfl]

I don't think you'll be able to use passed-in/dynamic table names in a stored procedure. SPs are pre-compiled for speed and if they don't even know what table they are touching then pre-compiling doesn't help. Consider passing in an integer value for each potential table name and have a big Case statement in the SP...

 

[SQLSister]

YOu can pass table names using dynamic SQL in a stored procedure. But if you do, you must grant rights at the table level not the stored procedure level.

exec("Select AirportName, AirportID, City , State, AirportType, FacilityUse, Country from "
+ @TableName)

This is an example, it should give you an idea of how to go about doing it.