Member Avatar for ravi035

how to create stored procedure for altering multiple coulmns ,
how to create stored for creating dynamic table and add multiple columns to created table

Edited by ravi035 because: n/a
  • 3 Contributors
  • 2 Replies
  • 82 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by prit005

Dani AI

Generated

asked how to alter/add multiple columns and create dynamic tables; correctly asked whether the table/columns are fixed or passed at runtime, and showed a basic proc template. Below is a practical pattern for dynamic "add/alter columns" that accepts a structured list (JSON), escapes identifiers with QUOTENAME, skips existing columns, and executes the DDL with sp_executesql. OPENJSON requires SQL Server 2016 or later.

CREATE PROCEDURE dbo.AddColumns_Dynamic
  @SchemaName SYSNAME = 'dbo',
  @TableName SYSNAME,
  @ColumnsJson NVARCHAR(MAX)  -- JSON: [{"name":"col","type":"INT","nullable":true},...]
AS
BEGIN
  SET NOCOUNT ON;
  IF @TableName IS NULL OR @ColumnsJson IS NULL RETURN;

  DECLARE @FullObjName NVARCHAR(400) = @SchemaName + '.' + @TableName;
  IF OBJECT_ID(@FullObjName) IS NULL
    RETURN;

  DECLARE @sql NVARCHAR(MAX);
  SELECT @sql = STUFF((
    SELECT '; ALTER TABLE ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName)
         + ' ADD ' + QUOTENAME(c.[name]) + ' ' + c.[type]
         + CASE WHEN ISNULL(c.[nullable],1)=0 THEN ' NOT NULL' ELSE ' NULL' END
    FROM OPENJSON(@ColumnsJson)
      WITH ([name] NVARCHAR(128) '$.name',[type] NVARCHAR(128) '$.type',[nullable] INT '$.nullable') c
    WHERE c.[name] IS NOT NULL AND c.[type] IS NOT NULL
      AND NOT EXISTS(SELECT 1 FROM sys.columns sc WHERE sc.object_id = OBJECT_ID(@FullObjName) AND sc.name = c.[name])
    FOR XML PATH(''), TYPE
  ).value('.', 'nvarchar(max)'), 1, 2, '');

  IF @sql IS NOT NULL
    EXEC sp_executesql @sql;
END;

Notes: validate/sanitize the supplied data-type strings (use a whitelist) before executing to avoid injection. Adding NOT NULL columns to populated tables will fail unless a default or backfill is provided. Consider transaction scope, permissions, and testing in non-production. See OPENJSON (JSON parsing), QUOTENAME (identifier escaping) and sp_executesql (dynamic execution) for details: OPENJSON, QUOTENAME, sp_executesql.

Recommended Answers

All 2 Replies

Member Avatar for adam_k

You will need to explain a bit your requests. By altering multiple columns, you mean update? Will the columns be fixed or are they to be passed as parameters (if this is the case, will the table also be a parameter?). Will the proc accept criteria as a value to a specific column or is it to be dynamic?

For the second part: What do you mean dynamic table?

Read here on how to create a stored procedure: http://msdn.microsoft.com/en-us/library/ms187926.aspx
and let us know if you have any problems.

Member Avatar for prit005

create Procedure ProcedureName
-- declare variable
as
begin
your DML Statement
End
go

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.