Create a UPSERT procedure from table name (SQL Server)

CreateUPSERTProcedure.sql

 
CREATE or ALTER PROC [dbo].[CreateInsertUpdateProcedure]
@TableName sysname,
@AutoCreate bit = 0
as

if((select count(TABLE_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName)>1)
begin

declare @variables  as varchar(max) = 'create or alter proc piu'+@TableName+CHAR(13)
 
SELECT @variables = @variables+  ('@' + replace(Column_Name,' ','_') + ' ' 
 + replace(replace(replace(DATA_TYPE,'varbinary','varbinary('+isnull(convert(varchar,CHARACTER_MAXIMUM_LENGTH),'max')+')'),'varchar','varchar('+isnull(convert(varchar,CHARACTER_MAXIMUM_LENGTH),'max')+')'),'-1','max')) + ' = ' +
  case ORDINAL_POSITION when 1 then '0' else 'null' end + ','  + CHAR(13) 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName and  COLUMN_NAME not in (select [name] from sys.computed_columns)

set @variables =  LEFT(@variables, LEN(@variables) - 2) + CHAR(13) + 'as' +CHAR(13) + CHAR(13) +'begin' + CHAR(13)
+ 'if (@'+ (SELECT Column_Name from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION = 1) + ' = 0 or @'+ (SELECT Column_Name from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION = 1) +' is null)'+ CHAR(13) + '	begin'+ CHAR(13)
 --insert
 declare @InsertCols varchar(max) = ''
 declare @InsertValues varchar(max) = ''

 select @InsertCols = @InsertCols + quotename(Column_Name) + ','  from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION > 1 and   COLUMN_NAME not in (select [name] from sys.computed_columns)
 set @InsertCols = LEFT(@InsertCols,LEN(@InsertCols)-1)
 select @InsertValues = @InsertValues + '@'+replace(Column_Name,' ','_')+ ',' from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION > 1 and   COLUMN_NAME not in (select [name] from sys.computed_columns)
 set @InsertValues = LEFT(@InsertValues,LEN(@InsertValues)-1)
 set @variables = @variables + '		INSERT INTO '+ @TableName +' ('+@InsertCols+') values ('+@InsertValues+')'+ CHAR(13)+'		set @' + (SELECT replace(Column_Name,' ','_') from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION = 1 and   COLUMN_NAME not in (select [name] from sys.computed_columns)) + ' = @@IDENTITY' + CHAR(13) + '	end' + CHAR(13)+'	else' + CHAR(13) + '	begin'+ CHAR(13)

 --update

 declare @UpdateCommand varchar(max) = ''
 select @UpdateCommand  = @UpdateCommand  +'		' + quotename(Column_Name) + ' = isnull(@'+ replace(Column_Name,' ','_') + ','+quotename(COLUMN_NAME)+'),'+ CHAR(13) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION > 1 and   COLUMN_NAME not in (select [name] from sys.computed_columns)
  set @UpdateCommand = LEFT(@UpdateCommand,LEN(@UpdateCommand)-2) + char(13)
 
 set @variables = @variables + '		UPDATE ' + @TableName + ' set '+ CHAR(13) + @UpdateCommand + '		where ' + 
 (SELECT Column_Name from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION = 1 and   COLUMN_NAME not in (select [name] from sys.computed_columns)) + ' = @' + (SELECT Column_Name from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION = 1) +CHAR(13) + '	end' + CHAR(13) +'	SELECT @'+(SELECT replace(Column_Name,' ','_') from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION = 1)+ ' as ' + (SELECT quotename(Column_Name) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION = 1 and   COLUMN_NAME not in (select [name] from sys.computed_columns)) + CHAR(13)+'end'
  

if(@AutoCreate = 1)
begin
exec(@variables)
print('Procedure created ')
print('piu' + @TableName)
end
else
begin
print @variables
end
end
else
begin
print 'Table not found'
end
GO