Stored procedure for inserting default value based on column type-CodePudding

I'm trying to figure out how to create a stored procedure which takes a table as input and then inserts values in all of the columns based on the column type.

For example: the table Dim.Name has two columns; the first is ID which is an int and the second is Name which is of type nvarchar:

ID(int)	Name(nvarchar)

I'm trying to figure out how to create a stored procedure that will take the table as a input and insert '-1' for all the int columns and 'Unknown' for all the nvarchar columns.

So the desired result would be

dbo.usp_InsertData(Dim.Name)

ID(int)	Name(nvarchar)
-1	Unknown

CodePudding user response：

You can build a dynamic INSERT using sys.columns

Exactly why you would want to do this is another question.

CREATE OR ALTER PROCEDURE WeirdInsert
  @schema sysname,
  @table  sysname
AS

DECLARE @sql nvarchar(max) = (
    SELECT '
INSERT '   QUOTENAME(@schema)   '.'   QUOTENAME(@table)   '
('   STRING_AGG(QUOTENAME(c.name), ', ')   ')
VALUES ('   STRING_AGG(
      CASE WHEN c.system_type_id IN (34,35,99,167,167,175,231,239)
           THEN '''Unknown'''
           WHEN c.system_type_id IN (48,52,56,59,60,62,106,108,122,127)
           THEN '-1'
           ELSE 'DEFAULT'
      END , ', ')
        ');'
    FROM sys.columns c
    JOIN sys.tables t ON t.object_id = c.object_id
    JOIN sys.schemas s ON s.schema_id = t.schema_id
    WHERE t.name = @table
      AND s.name = @schema
);

EXEC sp_executesql @sql;  -- can also pass parameters

SQL Fiddle