USE Util
GO
DROP PROCEDURE dbo.FixedLengthColumnBuilder
GO
DROP TYPE FixedLengthDefinition
GO
CREATE TYPE FixedLengthDefinition AS TABLE(
Id SMALLINT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
ColumnList VARCHAR(MAX) NOT NULL, -- ~ Delimited
ColumnAlias VARCHAR(256) NULL,
ColumnLength SMALLINT NOT NULL CHECK (ColumnLength BETWEEN 1 AND 4000),
IsChar BIT NOT NULL DEFAULT (1),
RightAlignNumeric BIT NOT NULL DEFAULT(1),
PadChar CHAR(1) NOT NULL DEFAULT (' '),
NullReplacement VARCHAR(128) NOT NULL DEFAULT (''),
StripString BIT NOT NULL DEFAULT (0),
Type VARCHAR(258) NOT NULL CHECK (TYPE IN ('DEFAULT','PAD_LEFT','PAD_RIGHT','REMOVE_DECIMAL','MULT_100_TO_INT','STRIP_WHITESPACE','yymmdd','yyyymmdd','mm/dd/yyyy','mon dd yyyy hh:miAM'))
)
GO
CREATE PROCEDURE dbo.FixedLengthColumnBuilder
@FixedLengthDefinition FixedLengthDefinition READONLY,
@MaxColumnLength INT = 1000 -- 0 For single columns
AS
SET NOCOUNT ON
IF NOT EXISTS ( SELECT *
FROM @FixedLengthDefinition )
BEGIN
DECLARE @SQL VARCHAR(MAX)
SET @SQL = '-- AN EASY WAY TO GET THE LIST OF AVAILABLE COLUMNS FROM A QUERY IS TO PUSH THE RESULTS INTO A TEMP TABLE WITH AN INVALID WHERE CLAUSE THIS WAY IT WON''T GENERATE ANY DATA
-- AND CALL THE SP_GETSTRUCT PROCEDURE TO GET THE COLUMN LIST IN GRID FORMAT AND THE TABLE DEFINITION IN THE MESSAGES SECTION
SELECT *
INTO #TEST
FROM Diablo.tCommon.DataSupplier
WHERE 1 = 0
EXEC SP_GETSTRUCT ''#TEST''
-- THIS IS THE TYPE DEFINITION, WHICH IS THE INPUT TO OUR PROC, PLEASE LOOK INTO THE COMMENTS EXPLAINING THE FIELDS
RETURN
FixedLengthDefinition AS TABLE(
Id SMALLINT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED, -- AUTO GENERATED
ColumnList VARCHAR(MAX) NOT NULL, -- IF COMMA DELIMITED WILL BE USED IN COALESCE, ELSE IN ISNULL, ~ DELIMITED
ColumnAlias VARCHAR(256) NULL,
ColumnLength SMALLINT NOT NULL CHECK (ColumnLength BETWEEN 1 AND 4000), -- FIXED LENGTH OF THE COLUMN
IsChar BIT NOT NULL DEFAULT (1), -- IF NOT SET TO 0 IT WILL BE CASTED TO VARCHAR FIRST
RightAlignNumeric BIT NOT NULL DEFAULT(1), -- WILL CONVERT [5500 ] TO [ 5500]
PadChar CHAR(1) NOT NULL DEFAULT ('' ''), -- WHAT IS THE PADDING CHARACTER 0, SPACE ... SINGLE CHARACTER ONLY
NullReplacement VARCHAR(128) NOT NULL DEFAULT (''''), -- ISNULL FUNCTION WILL USE THIS
StripString BIT NOT NULL DEFAULT (0), -- REMOVE BLANKS
Type VARCHAR(258) NOT NULL -- THEY MUST BE ONE OF THE BELOW, IF NOT WE NEED TO MODIFY TO CODE FOR ADDITIONAL TYPES
CHECK (TYPE IN (''DEFAULT'',''PAD_LEFT'',''PAD_RIGHT'',''REMOVE_DECIMAL'',''MULT_100_TO_INT'',''STRIP_WHITESPACE'',''yymmdd'',''yyyymmdd'',''mm/dd/yyyy'',''mon dd yyyy hh:miAM''))
)
GO
USE Util
-- THIS IS HOW TO FILL IT
DECLARE @FixedLengthDefinition FixedLengthDefinition
INSERT @FixedLengthDefinition ([ColumnList], ColumnAlias, [ColumnLength], [IsChar], [RightAlignNumeric], [PadChar], [NullReplacement], [StripString], [Type])
SELECT [ColumnList], Left(ColumnList, 5) AS ColumnAlias, [ColumnLength], [IsChar], [RightAlignNumeric], [PadChar], [NullReplacement], [StripString], [Type]
FROM (VALUES
(''DataSupplierId'', 10, 1, 1, 0, 0, 0, ''DEFAULT''),
(''PropId'', 10, 1, 1, 0, 0, 0, ''PAD_LEFT''),
(''AbsenteeOwnrInd'', 10, 1, 0, 0, 0, 0, ''PAD_LEFT''),
(''VTUUpdateTimestamp'', 10, 1, 1, 0, 0, 0, ''mon dd yyyy hh:miAM''),
(''UpdateTimestamp'', 10, 1, 1, 0, 0, 0, ''yyyymmdd''),
(''AddlLotInfo1~AddlLotInfo2~AdjAssdValAmt'', 10, 1, 1, 0, 0, 0, ''PAD_LEFT''),
(''AdjAssdValAmt'', 10, 1, 1, 0, 0, 0, ''PAD_LEFT''),
(''AgricDistCntyCd'', 10, 1, 1, 0, 0, 0, ''PAD_LEFT''),
(''AlleyTypCd'', 10, 1, 1, 0, 0, 0, ''STRIP_WHITESPACE''),
(''AmPageNbr'', 10, 1, 1, 0, 0, 0, ''PAD_LEFT''),
(''AmPageSfxNbr'', 10, 1, 1, 0, 0, 0, ''PAD_RIGHT''),
(''ApprAgricValTotal'', 10, 1, 1, 0, 0, 0, ''REMOVE_DECIMAL''),
(''ApprImpvValTotal'', 10, 1, 1, 0, 0, 0, ''REMOVE_DECIMAL''),
(''ApprLandValTotal'', 10, 1, 1, 0, 0, 0, ''REMOVE_DECIMAL''),
(''ApprOtherLanduseTotal'', 10, 1, 1, 0, 0, 0, ''REMOVE_DECIMAL''),
(''ApprPrevAgricAmt'', 10, 1, 1, 0, 0, 0, ''REMOVE_DECIMAL''),
(''ApprTimberAmt'', 10, 1, 1, 0, 0, 0, ''REMOVE_DECIMAL''),
(''ApprTotalValAmt'', 10, 1, 1, 0, 0, 0, ''MULT_100_TO_INT''),
(''AreaCd'', 10, 1, 1, 0, 0, 0, ''PAD_RIGHT''))
AS d ([ColumnList], [ColumnLength], [IsChar], [RightAlignNumeric], [PadChar], [NullReplacement], [StripString], [Type])
EXEC Util.dbo.FixedLengthColumnBuilder
@FixedLengthDefinition = @FixedLengthDefinition,
@MaxColumnLength = 1000
'
EXEC Util.dbo.PrintLargeText
@SQL
END
IF OBJECT_ID('tempdb..#FixedLengthDefinition') IS NOT NULL
DROP TABLE #FixedLengthDefinition
CREATE TABLE #FixedLengthDefinition (ID SMALLINT NOT NULL,
ColumnList VARCHAR(MAX) NOT NULL,
ColumnAlias VARCHAR(256) NULL,
ColumnLength SMALLINT NOT NULL,
IsChar BIT NOT NULL,
RightAlignNumeric BIT NOT NULL,
PadChar CHAR(1) NOT NULL,
NullReplacement VARCHAR(128) NOT NULL,
StripString BIT NOT NULL,
Type VARCHAR(258) NOT NULL,
ColumnId INT NOT NULL)
IF @MaxColumnLength = 0
INSERT #FixedLengthDefinition WITH (TABLOCK)
([ID],
[ColumnList],
ColumnAlias,
[ColumnLength],
[IsChar],
[RightAlignNumeric],
[PadChar],
[NullReplacement],
[StripString],
[Type],
[ColumnId])
SELECT [ID],
[ColumnList],
ISNULL(ColumnAlias, 'Column_' + CAST(id AS VARCHAR)) AS ColumnAlias,
[ColumnLength],
[IsChar],
[RightAlignNumeric],
[PadChar],
[NullReplacement],
[StripString],
[Type],
[ID] AS [ColumnId]
FROM @FixedLengthDefinition
ELSE
BEGIN
INSERT #FixedLengthDefinition WITH (TABLOCK)
([ID],
[ColumnList],
ColumnAlias,
[ColumnLength],
[IsChar],
[RightAlignNumeric],
[PadChar],
[NullReplacement],
[StripString],
[Type],
[ColumnId])
SELECT ID,
ColumnList,
'Column_' + CAST(1 AS VARCHAR) AS ColumnAlias,
ColumnLength,
IsChar,
RightAlignNumeric,
PadChar,
NullReplacement,
StripString,
Type,
1 AS ColumnId
FROM @FixedLengthDefinition a
CROSS APPLY (SELECT SUM (ColumnLength) AS SumLen FROM @FixedLengthDefinition b WHERE b.ID <= a.id) c
WHERE SumLen <= @MaxColumnLength
WHILE @@ROWCOUNT > 0
BEGIN
;
WITH cte
AS (SELECT ID,
ColumnList,
ColumnLength,
IsChar,
RightAlignNumeric,
PadChar,
NullReplacement,
StripString,
Type
FROM @FixedLengthDefinition a
WHERE NOT EXISTS ( SELECT *
FROM #FixedLengthDefinition b
WHERE a.Id = b.ID ))
INSERT #FixedLengthDefinition
(ID,
ColumnList,
ColumnAlias,
ColumnLength,
IsChar,
RightAlignNumeric,
PadChar,
NullReplacement,
StripString,
Type,
ColumnId)
SELECT ID,
ColumnList,
'Column_' + CAST(ci.ColumnId AS VARCHAR) AS ColumnAlias,
ColumnLength,
IsChar,
RightAlignNumeric,
PadChar,
NullReplacement,
StripString,
Type,
ci.ColumnId
FROM cte a
CROSS APPLY (SELECT SUM (ColumnLength) AS SumLen FROM cte b WHERE b.ID <= a.id) c
CROSS APPLY ( (SELECT MAX (ColumnId) + 1 AS ColumnId FROM #FixedLengthDefinition) ) ci
WHERE SumLen <= @MaxColumnLength
END
END
CREATE UNIQUE CLUSTERED INDEX FixedLengthDefinition ON #FixedLengthDefinition(id)
DECLARE @SizeStamp VARCHAR(30)= '/*sized*/' ;
WITH step1
AS (SELECT *
FROM #FixedLengthDefinition a
CROSS APPLY (SELECT CAST( ColumnLength AS VARCHAR) + @SizeStamp AS ColLenStr) cl
CROSS APPLY (SELECT CASE Type
WHEN 'REMOVE_DECIMAL' THEN CASE WHEN RightAlignNumeric = 1 THEN 'STR(CAST(' + ColumnList + ' AS INT), ' + ColLenStr + ')'
ELSE 'CAST(CAST(' + ColumnList + ' AS INT) AS VARCHAR)'
END
WHEN 'MULT_100_TO_INT'
THEN CASE WHEN RightAlignNumeric = 1 THEN 'STR(CAST(' + ColumnList + ' * 100 AS INT), ' + ColLenStr + ')'
ELSE 'CAST(CAST(' + ColumnList + ' * 100 AS INT) AS VARCHAR)'
END
WHEN 'STRIP_WHITESPACE' THEN 'REPLACE(' + ColumnList + ' , '' '', '''')'
WHEN 'yymmdd' THEN 'CONVERT(CHAR(' + ColLenStr + '), ' + ColumnList + ', 12)'
WHEN 'yyyymmdd' THEN 'CONVERT(CHAR(' + ColLenStr + '), ' + ColumnList + ', 112)'
WHEN 'mm/dd/yyyy' THEN 'CONVERT(CHAR(' + ColLenStr + '), ' + ColumnList + ', 101)'
WHEN 'mon dd yyyy hh:miAM' THEN 'CONVERT(CHAR(' + ColLenStr + '), ' + ColumnList + ', 0)'
ELSE CASE WHEN RightAlignNumeric = 1
AND ColumnList NOT LIKE '% %' THEN 'STR(' + ColumnList + ', ' + ColLenStr + ')'
WHEN IsChar = 0
AND ColumnList NOT LIKE '% %' THEN 'CAST(' + ColumnList + ' AS VARCHAR)'
ELSE ColumnList
END
END AS NewColumnList) tc
CROSS APPLY (SELECT CASE WHEN COUNT(*) > 1 THEN CASE WHEN IsChar = 0 THEN 'CAST('
ELSE ''
END + 'COALESCE('
ELSE 'ISNULL('
END + Util.dbo.StringConcat(Field, ', ') + ', ''' + REPLACE(NullReplacement, '''', '''''') + ''')'
+ CASE WHEN IsChar = 0 THEN ' AS VARCHAR'
ELSE ''
END AS ColList
FROM Util.dbo.ParseDelimited(NewColumnList, '~') b
WHERE b.Field <> '') k
CROSS APPLY (SELECT CASE Type
WHEN 'STRIP_WHITESPACE'
THEN 'CAST((SELECT CASE WHEN Pindex > 0 THEN SUBSTRING(LInput, Pindex, LEN(LInput) - Pindex + 1) ELSE '''' END FROM (SELECT LTRIM('
+ ColList + ') AS LInput) a CROSS APPLY (SELECT PATINDEX(''%^0%'', LInput) AS Pindex)b ) AS CHAR(' + ColLenStr + '))'
WHEN 'PAD_LEFT' THEN 'LEFT(' + ColList + ' + ''' + REPLICATE(PadChar, ColumnLength) + ''', ' + ColLenStr + ')'
WHEN 'PAD_RIGHT' THEN 'RIGHT(''' + REPLICATE(PadChar, ColumnLength) + ''' + ' + ColList + ', ' + ColLenStr + ')'
ELSE CASE WHEN ColList NOT LIKE '%' + @SizeStamp + '%' THEN 'CAST(' + ColList + ' AS CHAR(' + ColLenStr + '))'
ELSE ColList
END
END AS TypeCase) f)
SELECT @SQL = Util.dbo.StringConcat(SQL, ',
')
FROM (SELECT ColumnAlias + ' =' + Util.dbo.StringConcat(' ' + REPLACE(TypeCase, @SizeStamp, ''), ' +
') AS SQL
FROM step1
GROUP BY ColumnId,
ColumnAlias) p
EXEC Util.dbo.PrintLargeText
@SQL
GO