如何使用存储过程动态添加列或创建新表

Question

我在数据库中有一个表，其中列及其类型和表状态为new或old。

column     data_type      table_name    status
id          int            employee      new
name        varchar(20)    employee      new
region      varchar(20)    student        old

我想创建一个存储过程，其功能如下。

• 它应该从这个表中读取数据，如果表状态是新的，那么它应该创建一个具有给定列名及其数据类型的新表，但它应该在一个create语句中创建具有所有列的表。

• 如果状态是旧的，那么它应该执行alter命令并添加具有其数据类型的新列；如果我们必须在同一个表中添加多个列，那么应该使用单个alter语句添加它，而不是一次又一次。

现在我的问题是，如果所需的解决方案是一个接一个地添加列，我有办法，但我如何获取所有行并读取数据类型应添加到同一表中的所有列。。

我正在为我的方法提供伪代码。

declare temp variable
read data from table through select statement
set variable equal to data 
if status is new then run create command 
else run alter command.

但我的问题是，如果或者怎样才能找到将添加到同一列中的所有列。

Answer 1

您需要聚合两次，一次是对列，然后是对所有表。我还添加了一个nullable列：

DECLARE @sql nvarchar(max) = 
(SELECT STRING_AGG(stmt, NCHAR(10))
FROM (
SELECT
stmt = 'CREATE TABLE ' + 
QUOTENAME(table_name) +
' ( ' +
STRING_AGG(QUOTENAME(column) + ' ' + data_type + IIF(nullable, ' NULL', ' NOT NULL'), ', ') +
' );'
FROM Table
WHERE status = 'new'
GROUP BY table_name
) AS v);
SET @sql = @sql + NCHAR(10) + NCHAR(10) +
(SELECT STRING_AGG(stmt, NCHAR(10))
FROM (
SELECT
stmt = 'ALTER TABLE ' + 
QUOTENAME(table_name) +
' ALTER COLUMN ' +
NCHAR(10) +
STRING_AGG(QUOTENAME(column) + ' ' + data_type + IIF(nullable, ' NULL', ' NOT NULL'), ', ') +
';'
FROM Table
WHERE status = 'old'
GROUP BY table_name
) AS v);
PRINT @sql  --for testing
EXEC(@sql)