Jasinski Technical Wiki

Navigation

Home Page
Index
All Pages

Quick Search
»
Advanced Search »

Contributor Links

Create a new Page
Administration
File Management
Login/Logout
Your Profile

Other Wiki Sections

Software

PoweredBy

Update Statement Builder - SQL Server

RSS
Modified on Wed, Sep 06, 2017, 8:32 AM by Administrator Categorized as SQL Server
declare
     @TableNameExt      nvarchar(500)   = '[dbo].[Feature]'

;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      = 60
    ,Sort2 = 10
    ,SqlCode    = 'update ' + TableNameExt + ' set'
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 '? /* Param_' + 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 + '] = ? /* Param_' + convert(varchar(10), RowNum2) + ' */'
from
    PkeyFields

order by
    TableNameExt
    ,Sort1

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2018, Patrick Jasinski.