使用循环SQL动态创建表

Question

我正在用段循环动态创建表。将创建许多表。但是，运行代码时会遇到此错误。

转换varchar值' 时，

'转换失败

不确定为什么，因为，如果我将表名称编码为中，则可以正常工作。

旧代码 -

DECLARE @Count INT = (SELECT COUNT(*) FROM PayComponents) --This is how many 
times im going to loop.
DECLARE @Pcomp INT = '1' --starting at 1
DECLARE @Table VARCHAR(10) = 'Pcomp' --Part of the table name
DECLARE @TableNo VARCHAR(10) --declaring for adding onto table name.
DECLARE @TableName VARCHAR(10) --declare to hold full table name
 WHILE @Pcomp <= @Count --while 1 is less than or = to 61 loop
  BEGIN 
  DECLARE @str VARCHAR(MAX)
  SET @TableNo = CAST(@Pcomp AS VARCHAR(10))
  SET @TableName = @Table + @TableNo --Full table name... Pcomp1, Pcomp2, 
  Pcomp3, Pcomp4 etc

   SET @str = 'IF OBJECT_ID(''dbo.' + @TableName + ''', ''U'') IS NOT NULL //This is where i am getting the conversion error
   Drop Table ' + @TableName + '
   CREATE TABLE ' + @TableName + '
   (
   EeID INT,
   EeRef VARCHAR(10),
   Name VARCHAR(50),
   PayCompID SMALLINT,
   PayCompDesc VARCHAR(50),
   RateLastMonth MONEY,
   RateThisMonth MONEY
   )
   INSERT INTO ' + @TableName + ' (EeID, EeRef, Name, PayCompID, 
   PayCompDesc, 
   RateLastMonth)
   SELECT
   ed.EeID,
   ed.EeRef,
   CONCAT(Forename, '' '', Surname) AS ''Name'',
   eec.PCompID,
   eec.PCDescr,
   eec.Rate
   FROM EeDetails ed
   INNER JOIN UnityEeComponents eec ON eec.EeRef = ed.EeRef
   CROSS JOIN PayrollRuns pr
   WHERE eec.PCompID = ' + @Pcomp + ' AND pr.RunID = (SELECT RunID FROM 
   PayrollRuns WHERE RunDate = ''2019-04-30'')
   ORDER BY ed.EeID
   UPDATE ' + @TableName + '
   SET 
   ' + @TableName + '.RateThisMonth = EeComponents.ECRate
   FROM ' + @TableName + '
   INNER JOIN 
   EeComponents
   ON 
   ' + @TableName +'.EeID = EeComponents.EeID
   WHERE EeComponents.PCompID = ' + @Pcomp + ' AND EeComponents.RunID = 
   (SELECT RunID FROM PayrollRuns WHERE RunDate = ''2019-02-08'')
   SET ' + @Pcomp + ' = ' + @Pcomp + ' + 1;'
   END;

新代码 - 指出'commad已成功完成'

不创建表。

DECLARE @Count INT = (SELECT COUNT(*) FROM PayComponents) --This is how many 
times im going to loop
DECLARE @Pcomp INT = '1' --starting at 1
DECLARE @Table VARCHAR(10) = 'PComp' --Part of the table name
DECLARE @TableName VARCHAR(10) --declare to hold full table name
WHILE @Pcomp <= @Count --while 1 is less than or = to 61 loop
BEGIN
DECLARE @str VARCHAR(MAX)
SET @str = '
SET ' + @TableName + ' = CONCAT('+@Table+', '+CAST(@Pcomp AS VARCHAR(10))+')
IF OBJECT_ID(''dbo.' + QUOTENAME(@TableName) + ''', ''U'') IS NOT NULL
Drop Table ' + QUOTENAME(@TableName) + '
CREATE TABLE ' + QUOTENAME(@TableName) + '
(
EeID INT,
EeRef VARCHAR(10),
Name VARCHAR(50),
PayCompID SMALLINT,
PayCompDesc VARCHAR(50),
RateLastMonth MONEY,
RateThisMonth MONEY
)
INSERT INTO ' + QUOTENAME(@TableName) + ' (EeID, EeRef, Name, PayCompID, 
PayCompDesc, RateLastMonth, RateThisMonth)
SELECT
ed.EeID,
ed.EeRef,
CONCAT(Forename, '' '', Surname),
eec.PCompID,
eec.PCDescr,
eec.Rate,
''0.00''
FROM EeDetails ed
INNER JOIN UnityEeComponents eec ON eec.EeRef = ed.EeRef
CROSS JOIN PayrollRuns pr
WHERE eec.PCompID = ' + QUOTENAME(@Pcomp) + ' AND pr.RunID = (SELECT RunID 
FROM PayrollRuns WHERE RunDate = ''2019-04-30'')
ORDER BY ed.EeID
UPDATE ' + QUOTENAME(@TableName) + '
SET 
' + QUOTENAME(@TableName) + '.RateThisMonth = EeComponents.ECRate
FROM ' + QUOTENAME(@TableName) + '
INNER JOIN 
EeComponents
ON 
' + QUOTENAME(@TableName) +'.EeID = EeComponents.EeID
WHERE EeComponents.PCompID = ' + CAST(@Pcomp AS VARCHAR(10)) + ' AND 
EeComponents.RunID = (SELECT RunID FROM PayrollRuns WHERE RunDate = ''2019- 
02-08'');'
EXEC (@str)
SET @Pcomp = CAST(@Pcomp AS VARCHAR(10)) + 1;
END;

任何帮助或建议将不胜感激。

Answer 1

运算符会根据所应用的值的数据表进行不同的操作。它要么执行数字操作(添加(或连接字符串。您有(片段(

'....SET ' + @Pcomp + ' = ' + @Pcomp + ' + 1;'

@pcomp是一个int。另一个值是字符串。会发生什么？为了评估结果，必须将值转换为单个数据类型，以确定该怎么做。那么选择哪种数据类型？当您的代码没有明确指示要使用哪种 - 并且字符串几乎具有最低优先级时，就有一些规则。在这种情况下，引擎试图将您的字符串文字转换为INT，这将失败。

虽然解决您的直接问题，但您遇到了更大的问题。您的循环是无限的，因为您不会增加循环计数器。实际上，您不会执行生成的动态查询。全部可以解决 - 但更大的问题是为什么！

为什么需要在这里创建单独的表？您有一个正常化的问题，应该重新考虑。

最后，您将非常特定的日期进行了用于查询逻辑。尽管现在可能起作用，但我看不出这将以通用，持续的方式工作。