CODEWITHSUNDEEP
sqlsql-server
user14982470
user14982470

Reputation:

How to add Column or create new table dynamically using stored procedure

I have a table in database with column and its type and table status new or old.

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

I want to create a stored procedure which functions like below.

Now my question is i have approach if solution required is adding column one by one but how can i fetch all the rows and read all columns with data type which should be added in same table..

i am giving pseudo code for my approach.

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.

But my question is after if or else how can i find all the columns which will be added in same column.

Upvotes: 0

Views: 554

Answers (1)

Charlieface
Charlieface

Reputation: 71578

You need to aggregate twice, once on columns, then again all the tables. I added a nullable column also:

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)

Upvotes: 1

Related Questions