Page History: Update Statement Builder - SQL Server

Compare Page Revisions

1 0 Current 1 0

« Older Revision - Back to Page History - Current Revision

Page Revision: Wed, Sep 06, 2017, 8:15 AM

declare
     @TableNameExt      nvarchar(500)   = '[dbo].[SalesOrder]'

;with cte as (
    select
         *
         ,TableNameExt = '[' + SchemaName + '].[' + TableName + ']'
         ,ColumnNameExt = '[' + ColumnName + ']'
         ,MaxLen = max(len(ColumnName)) over (partition by SchemaName, TableName) + 2
		 ,RowNum = row_number() over (order by ColumnOrder) - 1
    from
        dbo.DbLayout
    where 1=1
        and '[' + SchemaName + '].[' + TableName + ']' = @TableNameExt
    )
,UpdateableFields as (
	select
		 *
		,RowNum2 = row_number() over (order by ColumnOrder) - 1
	from 
		cte
	where 1=1
		and cte.IsIdentity = 0
		and cte.IsCalculated = 0
)
,PkeyFields as (
	select
		*
		,RowNum2 = row_number() over (order by ColumnOrder) - 1 + (select count(2) from UpdateableFields)
	from
		cte
	where 1=1
		and cte.IsPkey = 1
)
select distinct
     TableNameExt
    ,Sort1 = 20
    ,Sort2 = 10
    ,SqlCode = '/*--- ' + TableNameExt + ' ---*/'
from
    cte
union

select distinct
     TableNameExt
    ,Sort1      = 60
    ,Sort2 = 10
    ,SqlCode    = 'update ' + TableNameExt
from
    cte
union
select distinct
     TableNameExt
    ,Sort1      = 70
    ,Sort2 = ColumnOrder
    ,SqlCode    = '    ' 
                    + case when RowNum2 = 0 then ' ' else ',' end 
                    + ColumnNameExt 
                    + REPLICATE(' ', MaxLen - len(ColumnNameExt) + 2) 
                    + '= ' 
                    + case ColumnName   
                        when 'CreatedBy' then '''system'''
                        when 'UpdatedBy' then '''system'''
                        when 'CreatedOn' then 'getutcdate()'
                        when 'UpdatedOn' then 'getutcdate()'
                        else '?'+ ' -- Parameter_' + convert(varchar(10), RowNum2) + ' - ' + uf.DataType + ' ' + case when uf.IsNullable = 1 then '' else 'not ' end + 'null'
                        end
                    
from
    UpdateableFields uf


union
select distinct
     TableNameExt
    ,Sort1 = 75
    ,Sort2 = 10
    ,SqlCode = 'where 1=1'
from
    cte

union
select distinct 
	 TableNameExt
	,Sort1 = 80
	,Sort2 = ColumnOrder
	,SqlCode = '    and [' + ColumnName + '] = ? -- Parameter_' + convert(varchar(10), RowNum2)
from
	PkeyFields

order by
     TableNameExt
    ,Sort1