USE Util ;
GO
SET NOCOUNT ON
GO
--#region Table Creation
DROP TABLE
FixedLengthDefinitionDetail ;
GO
DROP TABLE
FixedLengthDefinition ;
GO
CREATE TABLE FixedLengthDefinition (DefinitionID INT IDENTITY
NOT NULL,
CombinedColumnMaxlength SMALLINT NULL
CHECK (CombinedColumnMaxlength IS NULL
OR CombinedColumnMaxlength BETWEEN 1 AND 4000),
Description VARCHAR(500) NOT NULL,
PackageName VARCHAR(256) NULL,
UserName VARCHAR(256) NOT NULL
DEFAULT SUSER_NAME(),
DateAdded DATETIME NOT NULL
DEFAULT GETDATE() CONSTRAINT FixedLengthDefinition_PKC PRIMARY KEY CLUSTERED (DefinitionID),
CONSTRAINT FixedLengthDefinition_UNIQ UNIQUE NONCLUSTERED (Description)) ;
GO
CREATE TABLE FixedLengthDefinitionDetail (DetailID INT IDENTITY
NOT NULL,
DefinitionID INT NOT NULL,
ColumnList VARCHAR(MAX) NOT NULL,
ColumnListDelimiter VARCHAR(30) NULL
CHECK (ColumnListDelimiter IS NULL
OR ColumnListDelimiter <> ''),
DoNotQuoteName BIT NOT NULL
DEFAULT (0),
ColumnAlias VARCHAR(256) NULL,
ColumnLength SMALLINT NOT NULL
CHECK (ColumnLength BETWEEN 1 AND 4000),
ColumnType VARCHAR(128) NULL
CHECK (ColumnType LIKE '%CHAR%'
OR ColumnType LIKE '%DATE%'
OR ColumnType LIKE '%INT%'
OR ColumnType = 'BIT'
OR ColumnType LIKE '%NUMERIC%'
OR ColumnType LIKE '%decimal%'
OR columntype LIKE '%money%'
OR columntype LIKE '%float%'),
CharacterAddBetweenNumericAndAlpha VARCHAR(128) NULL,
CharacterAddBetweenAlphaAndNumeric VARCHAR(128) NULL,
NullReplacement VARCHAR(128) NULL,
ReplaceFrom VARCHAR(256) NULL,
ReplaceTo VARCHAR(256) NULL,
CharKeepLikeClause VARCHAR(256) NULL,
CharKeepNotLikeClause VARCHAR(256) NULL,
FormatDate VARCHAR(256) NULL
CHECK (FormatDate IS NULL
OR FormatDate IN ('yymmdd', 'yyyymmdd', 'mm/dd/yyyy', 'mon dd yyyy hh:miAM')),
RemoveLeadingZeros BIT NULL,
PadLeftCharacter CHAR(1) NULL,
PadRightCharacter CHAR(1) NULL,
NumericMultiplyBy INT NULL,
NumericFormat BIT NULL,
NumericScale SMALLINT NULL,
LeftTrim BIT NULL,
CONSTRAINT FixedLengthDefinitionDetail_PKC PRIMARY KEY CLUSTERED (DetailID),
CONSTRAINT CK_FixedLengthDefinitionDetail_Padding CHECK ((PadLeftCharacter IS NULL
AND PadRightCharacter IS NULL)
OR (PadLeftCharacter IS NOT NULL
AND PadRightCharacter IS NULL)
OR (PadLeftCharacter IS NULL
AND PadRightCharacter IS NOT NULL)),
CONSTRAINT CK_FixedLengthDefinitionDetail_CharacterAdd CHECK ((CharacterAddBetweenNumericAndAlpha IS NULL
AND CharacterAddBetweenAlphaAndNumeric IS NULL)
OR (CharacterAddBetweenNumericAndAlpha IS NOT NULL
AND CharacterAddBetweenAlphaAndNumeric IS NULL)
OR (CharacterAddBetweenNumericAndAlpha IS NULL
AND CharacterAddBetweenAlphaAndNumeric IS NOT NULL)),
CONSTRAINT FK_FixedLengthDefinitionDetail_FixedLengthDefinition FOREIGN KEY (DefinitionID) REFERENCES FixedLengthDefinition (DefinitionID),
CONSTRAINT CK_FixedLengthDefinitionDetail_Replace CHECK (ReplaceFrom IS NULL
OR (ReplaceFrom <> ''
AND ReplaceTo IS NOT NULL)),
CONSTRAINT CK_FixedLengthDefinitionDetail_LIKE CHECK ((CharKeepLikeClause IS NULL
AND CharKeepNotLikeClause IS NULL)
OR ((CharKeepLikeClause NOT LIKE '%[%]%'
AND CharKeepLikeClause <> '')
OR (CharKeepNotLikeClause NOT LIKE '%[%]%'
AND CharKeepNotLikeClause <> '')))) ;
GO
--#endregion
USE Util
GO
DROP PROCEDURE dbo.FixedLengthDefinitionIns
GO
CREATE PROCEDURE dbo.FixedLengthDefinitionIns
@Description VARCHAR(MAX) = NULL,
@PackageName VARCHAR(256) = NULL,
@CombinedColumnMaxlength SMALLINT = NULL,
@DefinitionId INT = 0 OUTPUT,
@SelectResults BIT = 1
AS
SET NOCOUNT ON
IF @Description IS NULL
BEGIN
EXEC sp_ExecTemplate
@ObjectName = 'dbo.FixedLengthDefinitionIns',
@PrintDefault = 0,
@DeclareReturn = 0,
@PrintReturnLine = 0,
@DoNotDeclareVar = 1
RETURN 30
END ;
SELECT @DefinitionId = DefinitionId
FROM dbo.FixedLengthDefinition
WHERE Description = @Description
IF @@ROWCOUNT = 1
BEGIN
RAISERROR ('@DefinitionId: %d already exists for Description: %s', 16, 1, @DefinitionId, @Description)
IF @SelectResults = 1
SELECT *
FROM dbo.FixedLengthDefinition
WHERE DefinitionId = @DefinitionId
RETURN 30
END
INSERT INTO dbo.FixedLengthDefinition
(CombinedColumnMaxlength,
Description,
PackageName)
SELECT @CombinedColumnMaxlength,
@Description,
@PackageName
SET @DefinitionId = SCOPE_IDENTITY()
IF @SelectResults = 1
SELECT *
FROM dbo.FixedLengthDefinition
WHERE DefinitionId = @DefinitionId
GO
USE Util ;
GO
DROP PROCEDURE dbo.FixedLengthColumnBuilder
GO
CREATE PROCEDURE dbo.FixedLengthColumnBuilder
@DefinitionId INT = NULL,
@Description VARCHAR(500) = NULL,
@PrintSQL BIT = 1,
@SQL VARCHAR(MAX) = '' OUTPUT
AS
SET NOCOUNT ON
DECLARE @CombinedColumnMaxlength INT
SELECT @DefinitionId = DefinitionId,
@CombinedColumnMaxlength = CombinedColumnMaxlength
FROM FixedLengthDefinition
WHERE Description = @Description
OR DefinitionId = @DefinitionId
IF @@ROWCOUNT <> 1
BEGIN
EXEC sp_ExecTemplate
@ObjectName = 'dbo.FixedLengthColumnBuilder',
@PrintDefault = 0,
@DeclareReturn = 0,
@PrintReturnLine = 0,
@DoNotDeclareVar = 1
RETURN 30
END
DECLARE @WorkTable TABLE (DetailID INT NOT NULL
PRIMARY KEY CLUSTERED,
ColumnList VARCHAR(MAX) NOT NULL,
ColumnListDelimiter VARCHAR(30) NULL,
DoNotQuoteName BIT NOT NULL,
ColumnAlias VARCHAR(256) NULL,
ColumnLength SMALLINT NOT NULL,
ColumnType VARCHAR(128) NULL,
CharacterAddBetweenNumericAndAlpha VARCHAR(128) NULL,
CharacterAddBetweenAlphaAndNumeric VARCHAR(128) NULL,
NullReplacement VARCHAR(128) NULL,
ReplaceFrom VARCHAR(256) NULL,
ReplaceTo VARCHAR(256) NULL,
CharKeepLikeClause VARCHAR(256) NULL,
CharKeepNotLikeClause VARCHAR(256) NULL,
FormatDate VARCHAR(256) NULL,
RemoveLeadingZeros BIT NULL,
PadLeftCharacter CHAR(1) NULL,
PadRightCharacter CHAR(1) NULL,
NumericMultiplyBy INT NULL,
NumericFormat BIT NULL,
NumericScale SMALLINT NULL,
ColumnId SMALLINT NULL,
ColLenStr VARCHAR(256) NOT NULL,
LeftTrim BIT NULL)
DECLARE @SizeStamp VARCHAR(30)= '/*sized*/'
INSERT @WorkTable
(DetailID,
ColumnList,
ColumnListDelimiter,
DoNotQuoteName,
ColumnAlias,
ColumnLength,
ColumnType,
CharacterAddBetweenNumericAndAlpha,
CharacterAddBetweenAlphaAndNumeric,
NullReplacement,
ReplaceFrom,
ReplaceTo,
CharKeepLikeClause,
CharKeepNotLikeClause,
FormatDate,
RemoveLeadingZeros,
PadLeftCharacter,
PadRightCharacter,
NumericMultiplyBy,
NumericFormat,
NumericScale,
ColumnId,
ColLenStr,
LeftTrim)
SELECT DetailID,
ColumnList,
ColumnListDelimiter,
DoNotQuoteName,
ColumnAlias,
ColumnLength,
ColumnType,
CharacterAddBetweenNumericAndAlpha,
CharacterAddBetweenAlphaAndNumeric,
NullReplacement,
ReplaceFrom,
ReplaceTo,
CharKeepLikeClause,
CharKeepNotLikeClause,
FormatDate,
RemoveLeadingZeros,
PadLeftCharacter,
PadRightCharacter,
NumericMultiplyBy,
NumericFormat,
NumericScale,
CASE WHEN ISNULL(@CombinedColumnMaxlength, 0) = 0 THEN ROW_NUMBER() OVER (PARTITION BY DefinitionID ORDER BY DetailID)
END AS ColumnId,
CAST(ColumnLength AS VARCHAR) + @SizeStamp AS ColLenStr,
LeftTrim
FROM dbo.FixedLengthDefinitionDetail
WHERE DefinitionID = @DefinitionId
IF @CombinedColumnMaxlength > 0
BEGIN
DECLARE @ColID INT = 1
WHILE 1 = 1
BEGIN
WITH s1
AS (SELECT ROW_NUMBER() OVER (ORDER BY DetailID) AS ROW,
DetailID,
ColumnLength,
ColumnId
FROM @WorkTable
WHERE ColumnId IS NULL)
UPDATE a
SET ColumnId = @ColID
FROM s1 a
CROSS APPLY (SELECT SUM (ColumnLength) AS SumLen FROM s1 b WHERE b.ROW <= a.ROW) B
WHERE SumLen <= @CombinedColumnMaxlength
IF @@ROWCOUNT = 0
BREAK
SET @ColID = @ColID + 1
END
END
UPDATE a
SET ColumnList = NewCol
FROM @WorkTable a
CROSS APPLY (SELECT CASE WHEN ColumnListDelimiter <> ''
THEN (SELECT 'COALESCE(' + Util.dbo.StringConcat(CASE WHEN LEFT(Field, 1) = '[' THEN Field
ELSE CASE WHEN b.DoNotQuoteName = 0 THEN QUOTENAME(Field)
ELSE Field
END
END, ', ') + ', ' + ISNULL(b.NullReplacement, 'NULL') + ')'
FROM @WorkTable b
CROSS APPLY Util.dbo.ParseDelimited(b.ColumnList, b.ColumnListDelimiter) p
WHERE p.Field <> ''
AND b.DetailId = a.DetailId
GROUP BY b.NullReplacement)
WHEN NullReplacement IS NOT NULL THEN 'ISNULL(' + CASE WHEN LEFT(a.ColumnList, 1) = '[' THEN a.ColumnList
ELSE CASE WHEN DoNotQuoteName = 0 THEN QUOTENAME(a.ColumnList)
ELSE a.ColumnList
END
END + ', ' + CASE WHEN NumericFormat = 1 THEN NullReplacement
ELSE '''' + REPLACE(NullReplacement, '''', '''''') + ''''
END + ')'
ELSE CASE WHEN LEFT(a.ColumnList, 1) = '[' THEN a.ColumnList
ELSE CASE WHEN DoNotQuoteName = 0 THEN QUOTENAME(a.ColumnList)
ELSE a.ColumnList
END
END
END AS NewCola) n
CROSS APPLY (SELECT CASE WHEN LeftTrim = 1 THEN 'LTRIM(' + NewCola + ')' ELSE NewCola END AS NewCol) b
UPDATE a
SET ColumnList = Col2
FROM @WorkTable a
CROSS APPLY (SELECT CASE WHEN CharKeepLikeClause <> ''
THEN '(SELECT ISNULL(Util.dbo.StringConcat(ch, ''''), '''') FROM Util.dbo.GetNumbersInline(1, LEN(' + ColumnList
+ ')) CROSS APPLY(SELECT SUBSTRING(' + ColumnList + ', digit, 1) AS ch)c WHERE c.ch LIKE ''%' + CharKeepLikeClause + '%'')'
WHEN CharKeepNotLikeClause <> ''
THEN 'ISNULL((SELECT Util.dbo.StringConcat(ch, ''''), '''') FROM Util.dbo.GetNumbersInline(1, LEN(' + ColumnList
+ ')) CROSS APPLY(SELECT SUBSTRING(' + ColumnList + ', digit, 1) AS ch)c WHERE c.ch NOT LIKE ''%' + CharKeepLikeClause + '%'')'
WHEN CharacterAddBetweenNumericAndAlpha IS NOT NULL
THEN '(SELECT CASE WHEN c.ci > 0 THEN LEFT(' + ColumnList + ', ci) + ''' + REPLACE(CharacterAddBetweenNumericAndAlpha, '''', '''''')
+ ''' + SUBSTRING(' + ColumnList + ', ci + 2, LEN(' + ColumnList + ')) WHEN c.di > 0 THEN LEFT(' + ColumnList + ', di) + '''
+ REPLACE(CharacterAddBetweenNumericAndAlpha, '''', '''''') + ''' + SUBSTRING(' + ColumnList + ', di+1, LEN(' + ColumnList
+ ')) ELSE ' + ColumnList + ' END FROM (SELECT PATINDEX( ''%[0-9][ ][A-Z]%'', ' + ColumnList
+ ') ci, PATINDEX( ''%[0-9][A-Z]%'', ' + ColumnList + ')di)c)'
WHEN CharacterAddBetweenAlphaAndNumeric IS NOT NULL
THEN '(SELECT CASE WHEN c.ci > 0 THEN LEFT(' + ColumnList + ', ci) + ''' + REPLACE(CharacterAddBetweenAlphaAndNumeric, '''', '''''')
+ ''' + SUBSTRING(' + ColumnList + ', ci + 2, LEN(' + ColumnList + ')) WHEN c.di > 0 THEN LEFT(' + ColumnList + ', di) + '''
+ REPLACE(CharacterAddBetweenAlphaAndNumeric, '''', '''''') + ''' + SUBSTRING(' + ColumnList + ', di+1, LEN(' + ColumnList
+ ')) ELSE ' + ColumnList + ' END FROM (SELECT PATINDEX( ''%[A-Z][ ][0-9]%'', ' + ColumnList
+ ') ci, PATINDEX( ''%[A-Z][0-9]%'', ' + ColumnList + ')di)c)'
WHEN ReplaceFrom <> ''
THEN 'REPLACE(' + ColumnList + ', ''' + REPLACE(ReplaceFrom, '''', '''''') + ''', ''' + REPLACE(ReplaceTo, '''', '''''') + ''')'
WHEN FormatDate = 'yymmdd' THEN 'CONVERT(CHAR(' + ColLenStr + '), ' + ColumnList + ', 12)'
WHEN FormatDate = 'yyyymmdd' THEN 'CONVERT(CHAR(' + ColLenStr + '), ' + ColumnList + ', 112)'
WHEN FormatDate = 'mm/dd/yyyy' THEN 'CONVERT(CHAR(' + ColLenStr + '), ' + ColumnList + ', 101)'
WHEN FormatDate = 'mon dd yyyy hh:miAM' THEN 'CONVERT(CHAR(' + ColLenStr + '), ' + ColumnList + ', 0)'
WHEN ISNULL(NumericMultiplyBy, 0) <> 0 THEN ColumnList + ' * ' + CAST(NumericMultiplyBy AS VARCHAR)
ELSE ColumnList
END AS Col2) e
UPDATE a
SET ColumnList = Col3
FROM @WorkTable a
CROSS APPLY (SELECT CASE WHEN RemoveLeadingZeros = 1
THEN 'CAST((SELECT CASE WHEN Pindex > 0 THEN SUBSTRING(LInput, Pindex, LEN(LInput) - Pindex + 1) ELSE '''' END FROM (SELECT LTRIM('
+ ColumnList + ') AS LInput) a CROSS APPLY (SELECT PATINDEX(''%[^0 ]%'', LInput) AS Pindex)b ) AS CHAR(' + ColLenStr + '))'
WHEN NumericFormat = 1
THEN 'STR(' + ColumnList + ', ' + ColLenStr + CASE WHEN NumericScale > 0 THEN ', ' + CAST(NumericScale AS VARCHAR)
ELSE ''
END + ')'
WHEN ColumnType NOT LIKE '%CHAR%'
AND ColumnList NOT LIKE '%' + @SizeStamp + '%' THEN 'CAST(' + ColumnList + ' AS VARCHAR)'
ELSE ColumnList
END AS Col3) f
UPDATE a
SET ColumnList = Col4
FROM @WorkTable a
CROSS APPLY (SELECT CASE WHEN PadLeftCharacter IS NOT NULL
THEN 'LEFT(LTRIM(' + ColumnList + ') + ''' + REPLICATE(ISNULL(NULLIF(PadLeftCharacter, ''), ' '), ColumnLength) + ''', ' + ColLenStr
+ ')'
WHEN PadRightCharacter IS NOT NULL
THEN 'RIGHT(''' + REPLICATE(ISNULL(NULLIF(PadRightCharacter, ''), ' '), ColumnLength) + ''' + LTRIM(' + ColumnList + '), ' + ColLenStr
+ ')'
ELSE ColumnList
END AS Col4) g
UPDATE a
SET ColumnList = Col5
FROM @WorkTable a
CROSS APPLY (SELECT CASE WHEN ColumnList NOT LIKE '%' + @SizeStamp + '%' THEN 'CAST(' + ColumnList + ' AS CHAR(' + ColLenStr + '))'
ELSE ColumnList
END AS Col5) h ;
WITH step1
AS (SELECT ColumnId,
ColumnList,
CASE WHEN @CombinedColumnMaxlength > 0 THEN 'Column_' + CAST(ColumnId AS VARCHAR)
ELSE ISNULL(ColumnAlias, 'Column_' + CAST(ColumnId AS VARCHAR))
END AS ColumnAlias
FROM @WorkTable)
SELECT @SQL = Util.dbo.StringConcat(SQL, ',
')
FROM (SELECT Util.dbo.StringConcat(' ' + REPLACE(ColumnList, @SizeStamp, ''), ' +
') + '
as ' + ColumnAlias AS SQL
FROM step1
GROUP BY ColumnId,
ColumnAlias) p
IF @PrintSQL = 1
EXEC Util.dbo.PrintLargeText
@SQL
GO
SET NOCOUNT ON
GO
USE TEMPORARY
IF OBJECT_ID('TestData') IS NOT NULL
DROP TABLE TestData
SELECT '/Smith Pete' AS Test1,
'1234 A Building change' AS Test2,
'ABS1234' AS Test3,
9 AS TestIns,
CAST(NULL AS VARCHAR(40)) AS TEST4,
'00 000adf' AS tEST5,
5004.77 AS Test6,
5005.897 AS Test7
INTO TestData
GO
--TRUNCATE TABLE Util.dbo.FixedLengthDefinitionDetail
--DELETE Util.dbo.FixedLengthDefinition
GO
DECLARE @DefinitionID INT,
@SQL VARCHAR(MAX)
EXEC Util.dbo.FixedLengthDefinitionIns
@Description = 'Gokhan Test 2',
@PackageName = NULL,
@CombinedColumnMaxlength = NULL,
@DefinitionID = @DefinitionID OUTPUT,
@SelectResults = 1
--#region Populate data
INSERT Util.dbo.FixedLengthDefinitionDetail
([DefinitionID],
[ColumnList],
[ColumnListDelimiter],
[ColumnAlias],
[ColumnLength],
[ColumnType])
SELECT @DefinitionID AS [DefinitionID],
ColumnName AS [ColumnList],
NULL AS [ColumnListDelimiter],
ColumnName AS [ColumnAlias],
40 AS [ColumnLength],
ColumnDef AS [ColumnType]
FROM TEMPORARY.Metadata.Columns
WHERE objectname = 'TestData'
EXEC Util.dbo.FixedLengthColumnBuilder
@DefinitionId = @DefinitionID,
@Description = NULL,
@PrintSQL = 1,
@SQL = @SQL OUTPUT
SET @SQL = '-- LETS FIND OUT OF THE DEFINITION OF THE OUTPUT COLUMNS
IF OBJECT_ID(''TEMPDB..#TEMP'') IS NOT NULL DROP TABLE #TEMP
SELECT
' + @SQL + '
INTO #TEMP
FROM Temporary..TestData
EXEC SP_GETSTRUCT ''#TEMP''
'
PRINT 'GO
' + @SQL + '
GO'
EXEC(@SQL)
GO