如何将表名作为存储过程的输入参数? [英] How to take table name as an input parameter to the stored procedure?

问题描述

我下面有一个小的存储过程.

I have a small stored procedure below.

我将表名作为存储过程的输入参数，以便计划将数据插入到临时表中并显示它们.这只是我的项目存储过程的一个很小的代码块.

I am taking the table name as an input parameter to the stored procedure so that I'm planning to insert the data into the temp table and display the same. This is just a tiny code block of my project stored procedure.

当我编译以下内容时，它会将select语句中的参数视为表变量并将错误抛出为:

When I am compiling the below, it is considering the parameter in the select statement as a table variable and throwing the error as:

必须声明表变量"@TableName".

Must declare the table variable "@TableName".

SQL:

CREATE PROCEDURE xyz @TableName Varchar(50) 
AS 
BEGIN 
SELECT TOP 10 * INTO #Temp_Table_One 
FROM @TableName 

SELECT * FROM #Temp_Table_One 
END

推荐答案

CREATE PROCEDURE xyz 
@TableName NVARCHAR(128) 
AS 
BEGIN 
  SET NOCOUNT ON;
  DECLARE @Sql NVARCHAR(MAX);

SET @Sql = N'SELECT TOP 10 * INTO #Temp_Table_One 
              FROM ' + QUOTENAME(@TableName)
          + N' SELECT * FROM #Temp_Table_One '

 EXECUTE sp_executesql @Sql

END

这篇关于如何将表名作为存储过程的输入参数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！