inside dynamic sql string have 2 variables:
col (column name) , val (column value).
within dynamic sql need check (within case statement) if col id column , if retrieve name column looking table.
example:
if col = 'locationid' , val = 7, need select locationname t_location locationid = 7
the 'when' statement follows:
set @sql = ' when ( select count(*) information_schema.columns table_name ''t_'' + replace(col,''id'','''') , (column_name = col or column_name = replace(col,''id'','''')+''name'') ) > 1... however unsure how achieve 'then' statement
+'then (select '+ <name_column> + ' ' + <table_name> + ' ' + <id_column> + ' = val)'; if col = 'locationid' name_column = 'locationname', table_name = 't_location' , id_column = 'locationid'
firstly retrieve name_column,
select column_name information_schema.columns table_name = ''t_'' + replace(col,'id','') , column_name = replace(col,'id','')+'name' except 'col' in dynamic sql string , throw error since statement outside of string. wouldn't work:
+'then (select '+ (select column_name information_schema.columns table_name = 't_' + replace(col,'id','') , column_name = replace(col,'id','')+'name' ) + ' from.....'; i have no choice use dynamic sql. there way make work? or better way? extract of monster dynamic sql string have keep in dynamic sql unfortunately in order rest work.
update
here full code in stored procedure:
(i want replace 'when col = '..id' then..' more dry)
declare @sql nvarchar(max) = 'create table #table (id int, datechanged datetime, change nvarchar(max), changedby varchar(500), batchname nvarchar(500), tablename nvarchar(500)); '; declare @tablename nvarchar(255) = ''; declare clcursor cursor select item dbo.udf_split_max(@tables,',') open clcursor fetch next clcursor @tablename while @@fetch_status = 0 begin if exists (select * information_schema.columns table_name = @tablename , column_name = @key) begin declare @archivetablename nvarchar(255) = dbo.replacefirstinstanceinstring(@tablename,'t_','archive_'), @columnname nvarchar(255) = '', @ordinalposition int = 0, @datatype nvarchar(255) = '', @charactermaxlength nvarchar(50) = '', @pk nvarchar(255) = @key, @archivepk nvarchar(255) = replace('archive'+dbo.propercase(replace(dbo.replacefirstinstanceinstring(@tablename,'t_',''),'_',' '))+'id',' ',''), @numofcolumns int = (select count(column_name) information_schema.columns table_name = @tablename ); set @sql = @sql + 'with cv ( select '+@pk+', userid, archivedateaddedutc, reason, [action], col, val (select '+@pk+', userid, archivedateaddedutc, reason, '; set @ordinalposition = 1; while @ordinalposition <= @numofcolumns begin set @columnname = convert(nvarchar(255),(select column_name information_schema.columns table_name = @tablename , ordinal_position = @ordinalposition)); if lower(@columnname) <> 'userid' , lower(@columnname) <> lower(@pk) , lower(@columnname) <> 'archivedateaddedutc' , lower(@columnname) <> 'reason' , lower(@columnname) <> 'action' begin set @sql = @sql + 'cast('+@columnname + ' varchar(8000)) '+@columnname+', '; end set @ordinalposition = @ordinalposition + 1; end set @sql = @sql + '[action] '+@archivetablename+') t unpivot (val col in ('; set @ordinalposition = 1; while @ordinalposition <= @numofcolumns begin set @columnname = convert(nvarchar(255),(select column_name information_schema.columns table_name = @tablename , ordinal_position = @ordinalposition)); if lower(@columnname) <> 'userid' , lower(@columnname) <> lower(@pk) , lower(@columnname) <> 'archivedateaddedutc' , lower(@columnname) <> 'reason' , lower(@columnname) <> 'action' begin set @sql = @sql +@columnname; if ((select top 1 ordinal_position information_schema.columns table_name = @tablename , lower(column_name) <> 'userid' , lower(column_name) <> lower(@pk) , lower(column_name) <> 'archivedateaddedutc' , lower(column_name) <> 'reason' , lower(column_name) <> 'action' order ordinal_position desc) <> @ordinalposition) begin set @sql = @sql + ', '; end else begin set @sql = @sql + ')) unpvt '+@pk+' = '+convert(nvarchar(100),@id); end end set @ordinalposition = @ordinalposition + 1; end set @sql = @sql + '), cvr ( select cv.*, row_number() on (partition '+@pk+', col order archivedateaddedutc) seqnum_all (select cv.*, row_number() on (partition '+@pk+', col, thegroup order archivedateaddedutc) seqnum_group (select cv.*, (row_number() on (partition '+@pk+', col order archivedateaddedutc) - row_number() on (partition '+@pk+', col, val order archivedateaddedutc) ) thegroup cv ) cv ) cv ), [raw] ( select cvr.*, cvrprev.val preval, '''+@tablename+''' [tablename] cvr left outer join cvr cvrprev on cvr.'+@pk+' = cvrprev.'+@pk+' , cvr.col = cvrprev.col , cvr.seqnum_all = cvrprev.seqnum_all + 1) insert #table (id,datechanged,change,changedby,batchname,tablename) select distinct top 15 '+convert(nvarchar(50),@id)+' [id], archivedateaddedutc [datechanged], case when [action] = ''insert'' case when tablename = ''t_pib'' , col = ''pibsml'' replace(dbo.spacebeforecap(col),''id'','''') + '' changed '' + convert(nvarchar(255),isnull(preval,0)) + '' '' + convert(nvarchar(255),val) else ''record inserted'' end when [action] = ''update'' case when len(col) < 4 col else replace(replace(replace(replace(dbo.spacebeforecap(col),''id'',''''),'' utc'',''''),''number'',''#''),''num'',''#'') end + '' changed '' + case when lower(col) = ''statusid'' (select statusname t_status convert(nvarchar(50),statusid) = preval) when lower(col) = ''locationid'' (select locationname t_location convert(nvarchar(50),locationid) = preval) when lower(col) = ''batchid'' (select batchname t_batch convert(nvarchar(50),batchid) = preval) when lower(col) = ''vatid'' (select vatname t_vat convert(nvarchar(50),vatid) = preval) when lower(col) = ''isarchived'' case when preval = ''1'' ''true'' else ''false'' end when lower(col) = ''isolateid'' (select isolatename t_isolate convert(nvarchar(50),isolateid) = preval) when lower(col) = ''batchtypeid'' (select batchtypename t_batch_type convert(nvarchar(50),batchtypeid) = preval) --when -- ( -- select count(*) -- -- information_schema.columns -- -- table_name ''t_'' + replace(col,''id'','''') , -- (column_name = col or column_name = replace(col,''id'','''')+''name'') -- ) > 1 --then -- ''condition satisfied''--(select '+ '<column>' +' '+'<table>'+' '+'<id>'+' = preval) when lower(col) ''%utc'' replace(convert(nvarchar(255),convert(date,preval),106), '' '',''-'') else preval end +'' ''+ case when lower(col) = ''statusid'' (select statusname t_status convert(nvarchar(50),statusid) = val) when lower(col) = ''locationid'' (select locationname t_location convert(nvarchar(50),locationid) = val) when lower(col) = ''batchid'' (select batchname t_batch convert(nvarchar(50),batchid) = val) when lower(col) = ''vatid'' (select vatname t_vat convert(nvarchar(50),vatid) = val) when lower(col) = ''isarchived'' case when val = ''1'' ''true'' else ''false'' end when lower(col) = ''isolateid'' (select isolatename t_isolate convert(nvarchar(50),isolateid) = val) when lower(col) = ''batchtypeid'' (select batchtypename t_batch_type convert(nvarchar(50),batchtypeid) = val) when lower(col) ''%utc'' replace(convert(nvarchar(255),convert(date,val),106), '' '',''-'') else val end when [action] = ''delete'' ''record deleted'' end [change], isnull((select username aspnet_users u u.userid = [raw].userid), ''user unknown'') [changedby], ' + -- batch name case when exists (select * information_schema.columns column_name = 'batchid' , table_name = @tablename) '(select top 1 b.batchname '+@tablename+' tbl inner join t_batch b on b.batchid = tbl.batchid tbl.'+@pk+' = '+convert(nvarchar(50),@id)+')' else '''''' end + ' [batchname], tablename [raw] (preval <> val collate latin1_general_cs_as or (preval null , thegroup = 0) or ([action] = ''delete'' , thegroup = 0)) , (case when [action] = ''insert'' ''record inserted'' when [action] = ''update'' replace(dbo.spacebeforecap(col),''number'',''#'') + '' changed ''+preval+'' ''+val when [action] = ''delete'' ''record deleted'' end) not null order archivedateaddedutc desc; '; end fetch next clcursor @tablename end close clcursor deallocate clcursor set @sql = @sql + '; select top '+convert(nvarchar(20),@numrecords)+' * #table (change <> ''record inserted'') order datechanged desc ;';
Comments
Post a Comment