sql server – Stored procedure to fill in Dim Value not available for any table

The code has been revised,

create procedure dbo.Dim_Seed_GenerateCode
@TableNameSource varchar (255)
as

declare @DimColumn varchar (255) =
(select QUOTENAME (c.name)
OF .sys.columns c
where c.object_id = object_id (@TableNameSource) and is_identity = 1)

declare @InsertValue varchar (max) =
(select STUFF ((
SELECT
& # 39;
+
case
when t.name as & # 39;% char% & # 39; and c.max_length> 2 then & # 39; & # 39; & # 39; N  A & # 39; & # 39; & # 39;
when t.name as & # 39;% char% & # 39; and c.max_length <= 2 then & # 39; & # 39; & # 39; A & # 39; & # 39; & # 39;
when t.name as & # 39;% numeric% & # 39; or t.name as & # 39;% decimal% & # 39; or t.name as & # 39;% int% & # 39; then & # 39; -1 & # 39;
when t.name as & # 39;% bit% & # 39; then & # 39; 0 & # 39;
when t.name as & # 39;% date% & # 39; and c.name as & # 39;% BegEffDate% & # 39; then & # 39; & # 39; & # 39; 7/4/1776 & # 39; & # 39; & # 39;
when t.name as & # 39;% date% & # 39; and c.name as & # 39;% EndEffDate% & # 39; then & # 39; & # 39; & # 39; 12 / 31/9999 & # 39; & # 39; & # 39;
when t.name as & # 39;% date% & # 39; then & # 39; & # 39; & # 39; 7/4/1776 & # 39; & # 39; & # 39;

finish
OF .sys.columns c
interior JOIN sys.types t
in t.user_type_id = c.user_type_id
and t.system_type_id = c.system_type_id
where c.object_id = object_id (@TableNameSource)
order by c.column_id
FOR XML PATH (& # 39; & # 39;), TYPE) .value (& # 39;. & # 39 ;, & # 39; Nvarchar (max) & # 39;), 1,2, & # 39 ; & # 39;))

declare @ColumnList varchar (max) =
(select STUFF ((
SELECT
& # 39;
+ QUOTENAME (c.name)
OF .sys.columns c
where c.object_id = object_id (@TableNameSource)
FOR XML PATH (& # 39; & # 39;), TYPE) .value (& # 39;. & # 39 ;, & # 39; Nvarchar (max) & # 39;), 1,2, & # 39 ; & # 39;))

declare @ColumnListUpdate varchar (max) =
(select STUFF ((
SELECT
& # 39;
+ QUOTENAME (c.name) + & # 39; = s. & # 39; + QUOTENAME (c.name)
OF .sys.columns c
where c.object_id = object_id (@TableNameSource) and is_identity = 0
FOR XML PATH (& # 39; & # 39;), TYPE) .value (& # 39;. & # 39 ;, & # 39; Nvarchar (max) & # 39;), 1,3, & # 39 ; & # 39;))


declare @GenerateInsertCode nvarchar (max) = & # 39;


SET IDENTITY_INSERT dbo. & # 39; + @TableNameSource + & # 39; ON

JOIN WITH [dbo]. & # 39; + quotename (@TableNameSource) + & # 39; like t
USING (
values
(
& # 39; +
@InsertValue +
& # 39;
)) s
(
& # 39; +
@ColumnList
+
& # 39;
)

In t & # 39; + @DimColumn + & # 39; = s. & # 39; + @DimColumn + & # 39;
When they are paired
THEN UPDATE
set
& # 39; + @ColumnListUpdate +
& # 39;
WHEN NOT PLAYED BY OBJECTIVE
SO
INSERT
(& # 39; +

@ColumnList
+
& # 39;
)
VALUES
(
& # 39;
+
@ColumnList + & # 39;
);


SET IDENTITY_INSERT & # 39; + @TableNameSource + & # 39; OFF & # 39;

print @GenerateInsertCode