0

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.

  • It should read data from this table and if table status is new then it should create new table with column name given and its data type ,but it should create table with all the columns in a single create statement.

  • If status is old then it should execute alter command and add new column with its data type and if we have to add more than one column in a same table then it should be added using single alter statement not again and again.

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.

Improve this question
2
  • This same question has been posted previously - but you start new account to post again? SO is not a free code-writing service. I suggest you start writing code and defining your requirements more precisely (preferably the latter first). If you are struggling with basic tsql looping logic and error handling, I don't really see how you can achieve your goal. Dynamic sql requires rather advanced tsql skills. Commented Jan 11, 2021 at 12:17
  • @SMor . . . If this is an exact duplicate, is there a reason you didn't close it? Commented Jan 11, 2021 at 12:27

1 Answer 1

Reset to default
1

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)
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

and sir if we have to change status of new to old after execution then how can we do that??
sir please tell what is the work of nchar (10) twice in se @sql statement
This will take whatever is in the table at the time you run it. NCHAR(10) is a new line

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.