使用存储过程（sp_executesql @sql）将动态SQL（T-SQL）插入（动态表名）

问题描述

我正在为将值插入表中的存储过程而苦苦挣扎。

I'm struggling with a stored procedure inserting values into a table.

表名是变量，例如Log1

The table name is variable, for example, Log1

该表包含以下列：实际，已调节，供应，预期， ControlValue 都键入 float 。

This table contains the following columns: Actual, Regulated, Supply, Expected, ControlValue all type float.

存储过程具有相同的输入参数，此外还具有表名称。

The stored procedure has the same input parameters and in addition to this the table name offcource.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[InsertIntoTable](
   @TableName nvarchar(50),
   @Actual real,
   @Regulated real,
   @Supply real,
   @Expected real,
   @Control_Value real)
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @columnList varchar(75)
  DECLARE @SQL NVARCHAR(MAX) -- dynamic SQL should always be Unicode

  SET @SQL = N'INSERT INTO dbo.' + @TableName +  '([Actual],[Regulated],[Supply],[Expected],[Control_Value])
        VALUES('+@Actual+','+@Regulated+', '+@Supply+','+@Expected+','+@Control_Value+')'

  EXEC sp_executesql @sql
END

推荐答案

首先，您需要诉诸动态SQL的事实表明一个严重的数据库设计问题：您有多个表存储相同的实体，其中包含一些标识符在表名中。

以您的示例为例，假设您拥有的表名为 log1 ， log2 ... logn ，应将其替换为一个名为 log 的表，并将该数字用作另一个该表中的列。

First, the fact that you need to resort to dynamic SQL indicates a serious database design problem: You have multiple tables that stores the same entity, holding some identifiers in the table name.
Taking your example, assuming the tables you have are named log1, log2...logn, you should replace them with a single table called log, and have that number as another column in this table.

我已经说过，在很多情况下，更改数据库结构不是一种选择-出于这个原因，我将向您显示解决此问题的方法（除了更改数据库结构之外，按照定义，其他任何方法都是解决方法，而不是解决方案。）

Having said that, I realize that in many cases, changing the database structure is not an option - and for that reason I'll show you a workaround for your problem (anything other than changing the database structure is by definition a workaround and not a solution).

您要做的第一件事是使用 sysname 作为表名的数据类型，而不是当前的 nvarchar（50）。

sysname 是SQL Server内部用于所有标识符的数据类型。

The first thing you want to do is to use sysname as the data type for the table name instead of your current nvarchar(50).
sysname is the data type used internally by SQL Server for all identifiers.

第二件事是确保表确实存在，并且它具有您期望的列。可以使用内置的 information_schema.columns 系统视图轻松完成此操作。

The second thing you is to make sure the table actually exist, and that it has the columns you expect. This can easily be done using the built in information_schema.columns system view.

接下来，您要处理您的参数作为参数-不将它们连接到动态SQL字符串中，而是将它们作为参数传递给 sp_executeSql 。

Next, you want to treat your parameters as parameters - not to concatenate them into the dynamic SQL string, but pass them as parameters to sp_executeSql.

最后，您要防止表名中的时髦字符，因此在将其连接到SQL时使用 QUOTENAME ：

And finally, you want to safeguard against funky chars in the table name, so you use QUOTENAME when concatenate it to your SQL:

ALTER PROCEDURE [dbo].[InsertIntoTable](
   @TableName sysname,
   @Actual real,
   @Regulated real,
   @Supply real,
   @Expected real,
   @Control_Value real)
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @ColumnCount int;
  SELECT @ColumnCount = COUNT(*)
  FROM Information_schema.Columns
  WHERE Table_Name = @TableName
  AND COLUMN_NAME IN([Actual],[Regulated],[Supply],[Expected],[Control_Value])

  IF @ColumnCount < 5 RETURN;


  DECLARE @SQL NVARCHAR(MAX) -- dynamic SQL should always be Unicode

  SET @SQL = N'INSERT INTO dbo.' + QUOTENAME(@TableName) +  '([Actual],[Regulated],[Supply],[Expected],[Control_Value])
        VALUES(@Actual, @Regulated, @Supply, @Expected, @Control_Value)'

  EXEC sp_executesql
      @sql,
      N'@Actual real, @Regulated real, @Supply real, @Expected real, @Control_Value real',
      @Actual, @Regulated, @Supply, @Expected, @Control_Value
END

有关详细说明，请查看出我的博客文章。

For a more detailed explanation, check out my blog post entitled The do’s and don’ts of dynamic SQL for SQL Server.

这篇关于使用存储过程（sp_executesql @sql）将动态SQL（T-SQL）插入（动态表名）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！