Passing a Table name as a parameter

Question

I am tasked with keeping several tables updated with information to be brought in from an external source. To this end, I've been hunting online for ways to pass table names as parameters, and all the answers are complex and/or throw errors (such as the below: "Incorrect syntax near 'Table' error shows)

CREATE PROCEDURE sp_Insert_Delta 
-- Add the parameters for the stored procedure here
@tableName Table READONLY

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Delete rows in MIRROR database where ID exists in the DELTA database
Delete from [S1].[MIRROR].[dbo].@tableName
Where [ID] in (Select [ID] from [S2].[DELTAS].[dbo].@tableName)

-- Insert all deltas
Insert Into [S1].[MIRROR].[dbo].@tableName
Select * from [S2].[DELTAS].[dbo].@tableName

END
GO

This script works just fine when named explicitly, so how can I parameterize the table name?

Thank you,

Nate

Side note: if this is SQL Server, you should **not** use the `sp_` prefix for your stored procedures. Microsoft has [reserved that prefix for its own use (see *Naming Stored Procedures*)](http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx), and you do run the risk of a name clash sometime in the future. [It's also bad for your stored procedure performance](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix). It's best to just simply avoid `sp_` and use something else as a prefix - or no prefix at all! — marc_s, Aug 04 '14 at 17:27
Does this answer your question? [How should I pass a table name into a stored proc?](https://stackoverflow.com/questions/1246760/how-should-i-pass-a-table-name-into-a-stored-proc) — Michael Freidgeim, Oct 27 '21 at 17:37

score 3 · Answer 1 · answered Aug 04 '14 at 17:18

3

use dynamic SQL

DECLARE @sql as varchar(4000)
SET @sql = 'Delete from [S1].[MIRROR].[dbo].' + @tableName
+ ' Where [ID] in (Select [ID] from [S2].[DELTAS].[dbo].' + @tableName + ')'
EXEC(@sql)

As an example

answered Aug 04 '14 at 17:18

RustyH

473
7
22

Dynamic sql is a good solution--no disagreement with that. I'd suggest making it parameterized via sp_executesql, however. There's a [couple of advantages](http://blogs.msdn.com/b/turgays/archive/2013/09/18/exec-vs-sp-executesql.aspx)--protection against sql injection being the biggest advantage. – Dave Mason Aug 04 '14 at 17:25
Very good point, I'm pretty new to Dynamic sql (and sql period) myself but have been using it like crazy lately at work. Thanks for the link – RustyH Aug 04 '14 at 17:28
I was going to edit my answer to include the Sp_executesql but was wondering how you would add the tablename var since it is just the last of the table name and would still need to be added to the "[S2].[DELTAS].[dbo]." – RustyH Aug 04 '14 at 17:43

score 3 · Answer 2 · answered Aug 04 '14 at 17:19

Obligatory preamble about how you should never do this because of SQL injection vulnerability.

In other words you want to validate the input, I give an example how to do that below:

CREATE PROCEDURE sp_Insert_Delta 
-- Add the parameters for the stored procedure here
@tableName varchar(max) READONLY

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

IF EXISTS(SELECT 1 FROM sys.tables WHERE [name] = @tableName)
BEGIN 

  -- Delete rows in MIRROR database where ID exists in the DELTA database
  exec("Delete from [S1].[MIRROR].[dbo]."+@tableName"+
  " Where [ID] in (Select [ID] from [S2].[DELTAS].[dbo]."+@tableName);

  -- Insert all deltas
  exec("Insert Into [S1].[MIRROR].[dbo]."+@tableName)
  exec("Select * from [S2].[DELTAS].[dbo]."+@tableName)
END
-- ELSE handle error.

END
GO

NB I don't have access to an SQL Server right now so this might have a typo.

score 2 · Accepted Answer · edited May 23 '17 at 10:32

The short answer is that you can't parameterize the table name.

The longer answer is that can can accomplish what you want via dynamic SQL. It looks like you're using SQL Server. See the question Dynamic SQL - EXEC(@SQL) versus EXEC SP_EXECUTESQL(@SQL) for details.

One should really not, however, that a need to drop arbitrary table names into a query is a code smell indicating to me that you've got architectural problems with your database design and probably with your E-R model.

Passing a Table name as a parameter

3 Answers3