CREATE PROCEDURE dbo.LineFunctionGeneratorFixedLength
@FunctionName VARCHAR(256) = NULL,
@ColumnDefFixedLength dbo.ColumnDefFixedLength READONLY,
@Cast BIT = 1,
@ShowInput BIT = 1,
@ListErrors BIT = 1,
@SortInputAlpha BIT = 1,
@SortOutputAlpha BIT = 1,
@SQL VARCHAR(MAX) = NULL OUTPUT,
@PrintSQL BIT = 1,
@PrintSample BIT = 1
AS
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
DECLARE @Prec TABLE (InputColumnName VARCHAR(256) NOT NULL,
CharMaxLen SMALLINT NULL,
MinValue VARCHAR(30) NULL,
MaxValue VARCHAR(30) NULL)
DECLARE @DBname VARCHAR(256),
@UtilFuncDbName VARCHAR(256)
DECLARE @ColumnCount INT = (SELECT COUNT (*) FROM @ColumnDefFixedLength)
IF @ShowInput = 1
AND @Cast = 1
AND (@ColumnCount * 2 + 1) > 1024
SET @ShowInput = 0
IF @ColumnCount = 0
BEGIN
PRINT '--[dbo].[ColumnDefFixedLength] AS TABLE (Id SMALLINT IDENTITY NOT NULL PRIMARY KEY CLUSTERED CHECK (Id BETWEEN 1 AND 1024),
-- InputColumnName VARCHAR(256) NOT NULL UNIQUE NONCLUSTERED,
-- OutputColumnName VARCHAR(256) NULL CHECK (OutputColumnName IS NULL OR OutputColumnName <> ''''),
-- Included BIT NOT NULL DEFAULT 1,
-- FixedLength SMALLINT NOT NULL CHECK (FixedLength BETWEEN 1 AND 8000),
-- TextQualifier VARCHAR(10) NULL CHECK (TextQualifier IS NULL OR TextQualifier <> ''''),
-- DataType VARCHAR(256) NULL CHECK (DataType IS NULL
-- OR DataType IN (''tinyint'', ''smallint'', ''sysname'', ''int'', ''real'', ''money'', ''smallmoney'',
-- ''float'', ''bit'', ''bigint'', ''date'', ''datetime'', ''smalldatetime'')
-- OR DataType LIKE ''datetime2%''
-- OR DataType LIKE ''time%''
-- OR DataType LIKE ''decimal(%,%)''
-- OR DataType LIKE ''numeric(%,%)''
-- OR DataType LIKE ''char(%)''
-- OR DataType LIKE ''varchar(%)''
-- OR DataType LIKE ''nchar(%)''
-- OR DataType LIKE ''nvarchar(%)''
GO
USE [Util]
GO
SET NOCOUNT ON
GO
DECLARE @ColumnDefFixedLength dbo.ColumnDefFixedLength
INSERT @ColumnDefFixedLength
(InputColumnName,
OutputColumnName,
Included,
FixedLength,
TextQualifier,
DataType)
SELECT InputColumnName,
InputColumnName as OutputColumnName,
1 AS Included,
FixedLength,
NULL AS TextQualifier,
DataType
FROM ( VALUES ( ''C1'', 10, ''VARCHAR(30)''), ( ''C2'', 3, ''VARCHAR(5)''), ( ''C3'', 7, ''VARCHAR(5)''), ( ''C4'', 5, ''int''), ( ''C5'', 3, ''tinyint''), ( ''C6'', 10, ''datetime2(2)'') ) ddata (InputColumnName, FixedLength, DataType)
EXEC dbo.LineFunctionGeneratorFixedLength
@FunctionName = ''Temporary.dbo.TestFixedLength'',
@ColumnDefFixedLength = @ColumnDefFixedLength,
@Cast = 1, -- Will convert to the given datatype
@ShowInput = 1, -- Will show casted and original both
@ListErrors = 1, -- Will have an additional colum for errors
@SortInputAlpha = 1, -- Sorts columns alphabetically by input names
@SortOutputAlpha = 0, -- Sorts columns alphabetically by output names
@PrintSQL = 1, -- Prints results
@PrintSample = 1 -- Prints sample select
'
RETURN
END
SELECT @SQL = 'OutputColumnName must be unique, duplicates listed below found !
' + dbo.StringConcat('OutputColumn: ' + OutputColumnName + ', COUNT: ' + CAST(Counter AS VARCHAR), '
')
FROM (SELECT OutputColumnName,
COUNT(*) AS Counter
FROM @ColumnDefFixedLength
WHERE OutputColumnName IS NOT NULL
GROUP BY OutputColumnName
HAVING COUNT(*) > 1) k
IF @SQL <> ''
BEGIN
RAISERROR (@SQL,16,1)
RETURN 30
END
IF @Cast = 1
BEGIN
DECLARE @AlterTable VARCHAR(MAX)
SELECT @AlterTable = 'ALTER TABLE #DataTypes ADD ' + dbo.StringConcat('[' + LTRIM(RTRIM(PARSENAME(InputColumnName, 1))) + '] ' + DataType, ',
')
FROM @ColumnDefFixedLength
WHERE DataType IS NOT NULL
IF @AlterTable <> ''
BEGIN
IF OBJECT_ID('tempdb..#DataTypes') IS NOT NULL
DROP TABLE #DataTypes
CREATE TABLE #DataTypes ([__ID] INT)
EXEC(@AlterTable)
INSERT @Prec
(InputColumnName,
CharMaxLen,
MinValue,
MaxValue)
SELECT c.name,
CASE WHEN ty.name LIKE 'n%CHAR'
AND c.max_length > 0 THEN c.max_length / 2
WHEN ty.name LIKE '%CHAR'
AND c.max_length > 0 THEN c.max_length
END AS CharMaxLen,
CAST(ISNULL(et.MinValue, -1.0 * POWER(10.0, CalcPrec) + 1.0) AS VARCHAR) AS MinValue,
CAST(ISNULL(et.MaxValue, POWER(10.0, CalcPrec) - 1.0) AS VARCHAR) AS MaxValue
FROM tempdb.sys.columns c (NOLOCK)
INNER JOIN sys.types ty (NOLOCK) ON ty.user_type_id = c.user_type_id
CROSS APPLY (SELECT CASE WHEN ty.NAME NOT LIKE '%DATE%'
AND ty.NAME NOT LIKE '%time%'
AND c.PRECISION > 0 THEN c.PRECISION - ISNULL(c.scale, 0)
END AS CalcPrec) cp
LEFT OUTER JOIN dbo.ExactNumericDataTypeRanges et ON et.TypeName = ty.name COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE OBJECT_ID = OBJECT_ID('tempdb..#DataTypes')
END
END
SELECT @DBname = ISNULL('[' + LTRIM(RTRIM(PARSENAME(@FunctionName, 3))) + ']', '[Temporary]'),
@FunctionName = ISNULL('[' + LTRIM(RTRIM(PARSENAME(@FunctionName, 2))) + '].', '[dbo].') + '[' + LTRIM(RTRIM(PARSENAME(@FunctionName, 1))) + ']'
SELECT @FunctionName = ISNULL(@FunctionName,
'[do].[' + SUSER_NAME() + '_' + CONVERT(VARCHAR(30), GETDATE(), 112) + '_' + REPLACE(CONVERT(VARCHAR(8), GETDATE(), 108), ':', '')
+ ']'),
@UtilFuncDbName = CASE WHEN @DbName = '[Util]' THEN ''
ELSE @DbName + '.'
END ;
WITH s1
AS (SELECT Id,
ColList,
ErrList,
CNAllIN,
CNAllOUT,
CNAllINIncluded,
--CNAllOUTIncluded,
CNAllINIncludedTmp,
CNAllOUTIncludedTmp,
SLen,
SStart,
ParseDate,
ParseNumeric,
DataType,
MinValue,
MaxValue,
CharMaxLen,
FixedLength,
REPLACE(TextQualifier, '''', '''''') AS TextQualifier,
MAX(SIGN(LEN(TextQualifier))) OVER (PARTITION BY (SELECT 0)) AS TextQualified,
LEN(TextQualifier) AS TQL
FROM @ColumnDefFixedLength a
CROSS APPLY (SELECT CAST(ISNULL(SUM(FixedLength), 0) + 1 AS VARCHAR) AS SStart
FROM @ColumnDefFixedLength b
WHERE b.iD < A.iD) c
CROSS APPLY (SELECT CAST( FixedLength AS VARCHAR) AS SLen) sl
LEFT OUTER JOIN @Prec pr ON a.InputColumnName = pr.InputColumnName
CROSS APPLY (SELECT REPLACE(LTRIM(RTRIM(PARSENAME(a.InputColumnName, 1))), '''', '''''') AS CNAllIN,
REPLACE(LTRIM(RTRIM(PARSENAME(ISNULL(a.OutputColumnName, a.InputColumnName), 1))), '''', '''''') AS CNAllOUT,
CASE WHEN @Cast = 1
AND DataType IN ('tinyint', 'smallint', 'int', 'real', 'money', 'smallmoney', 'float', 'bit', 'bigint')
OR DataType LIKE 'decimal(%,%)'
OR DataType LIKE 'numeric(%,%)' THEN 1
ELSE 0
END AS ParseNumeric,
CASE WHEN @Cast = 1
AND DataType IN ('date', 'datetime', 'smalldatetime')
OR DataType LIKE 'datetime2%'
OR DataType LIKE 'time%' THEN 1
ELSE 0
END AS ParseDate) b
CROSS APPLY (SELECT CASE WHEN Included = 1 THEN CNAllIN
END AS CNAllINIncluded,
CASE WHEN Included = 1 THEN CNAllOUT
END AS CNAllOUTIncluded) k
CROSS APPLY (SELECT CASE WHEN @ShowInput = 1 THEN CNAllINIncluded
ELSE CNAllOUTIncluded
END AS CNErr,
CASE WHEN @ShowInput = 1
AND @Cast = 1 THEN '[' + CNAllINIncluded + '_IN]'
WHEN @ShowInput = 1 THEN '[' + CNAllINIncluded + '_IN]'
END AS CNAllINIncludedTmp,
CASE WHEN @ShowInput = 1
AND @Cast = 1 THEN '[' + CNAllINIncluded + '_IN]'
WHEN @ShowInput = 1 THEN '[' + CNAllINIncluded + ']'
END AS CNAllINIncludedOut,
CASE WHEN @ShowInput = 1
AND @Cast = 1 THEN '[' + CNAllOUTIncluded + '_TMP]'
WHEN @Cast = 1 THEN '[' + CNAllOUTIncluded + '_TMP]'
END AS CNAllOUTIncludedTmp,
CASE WHEN @ShowInput = 1
AND @Cast = 1 THEN '[' + CNAllOUTIncluded + '_OUT]'
WHEN @Cast = 1 THEN '[' + CNAllOUTIncluded + ']'
END AS CNAllOUTIncludedOut) kd
CROSS APPLY (SELECT SUBSTRING(ISNULL(',[' + CNAllINIncluded + '] AS ' + CNAllINIncludedOut, '') + ISNULL(','
+ CASE WHEN @Cast = 1
THEN CASE WHEN ParseNumeric = 1
THEN ISNULL('CASE WHEN '
+ CNAllOUTIncludedTmp
+ ' BETWEEN '
+ MinValue
+ ' AND '
+ MaxValue
+ ' THEN CAST('
+ CNAllOUTIncludedTmp
+ ' AS '
+ DataType
+ ') END AS '
+ CNAllOUTIncludedOut,
'CAST('
+ CNAllOUTIncludedTmp
+ ' AS '
+ DataType
+ ') AS '
+ CNAllOUTIncludedOut)
ELSE CNAllOUTIncludedTmp
+ ' AS '
+ CNAllOUTIncludedOut
END
ELSE ',[' + CNAllINIncluded
+ '] AS '
+ CNAllOUTIncludedOut
END, ''), 2, 1000) AS ColList,
CASE WHEN ParseNumeric = 1
THEN 'CASE WHEN [' + CNAllINIncluded + '] <> '''' AND ' + CNAllOUTIncludedTmp + ' IS NULL THEN '',' + CNErr
+ ':Failed_Cast'' ' + ISNULL('WHEN ' + CNAllOUTIncludedTmp + ' NOT BETWEEN ' + MinValue + ' AND ' + MaxValue
+ ' THEN '',' + CNErr + ':Overflowed'' ', '') + 'WHEN ' + CNAllOUTIncludedTmp
+ ' <> CAST(' + CNAllOUTIncludedTmp + ' AS ' + DataType + ') THEN '',' + CNErr + ':Lost_Precision'' ELSE '''' END'
WHEN ParseDate = 1
THEN 'CASE WHEN [' + CNAllINIncluded + '] <> '''' AND ' + CNAllOUTIncludedTmp + ' IS NULL THEN '',' + CNErr
+ ':Cast_Failed'' ELSE '''' END'
WHEN CharMaxLen <> ''
THEN 'CASE WHEN [' + CNAllINIncluded + '] <> ' + CNAllOUTIncludedTmp + ' THEN '',' + CNErr
+ ':Lost_Char'' ELSE '''' END'
END AS ErrList) d),
s2
AS (SELECT dbo.StringConcat(ColList, ',
') AS ColOut,
dbo.StringConcat(ErrList, '
+ ') AS ErrOut
FROM (SELECT TOP 999999
ColList,
ErrList
FROM s1
ORDER BY CASE WHEN @SortOutputAlpha = 1 THEN CNAllOUT
ELSE ''
END,
CASE WHEN @SortInputAlpha = 1 THEN CNAllIN
ELSE ''
END,
Id) k)
SELECT @SQL = ISNULL('USE ' + @DBName + '
GO
', '') + 'IF OBJECT_ID(''' + @FunctionName + ''') IS NOT NULL DROP FUNCTION ' + @FunctionName + '
GO
CREATE FUNCTION ' + @FunctionName + '(@Input VARCHAR(MAX))
RETURNS TABLE
AS
RETURN
' + CASE WHEN @Cast = 0
THEN 'WITH o AS(SELECT
' + dbo.StringConcat(' ' + CASE WHEN TQL > 0
THEN @UtilFuncDbName + 'dbo.ParseTextQualified(''' + TextQualifier + ''', SUBSTRING(@Input, ' + SStart + ', ' + SLen + '))'
ELSE 'SUBSTRING(@Input, ' + SStart + ', ' + SLen + ')'
END + ' AS [' + CNAllINIncluded + ']', ',
') + ')
SELECT
' + MAX(ColOut) + '
FROM o' ELSE 'WITH o AS(SELECT
' + dbo.StringConcat(' ' + CASE WHEN TQL > 0
THEN @UtilFuncDbName + 'dbo.ParseTextQualified(''' + TextQualifier + ''', SUBSTRING(@Input, ' + SStart + ', ' + SLen + '))'
ELSE 'SUBSTRING(@Input, ' + SStart + ', ' + SLen + ')'
END + ' AS [' + CNAllINIncluded + ']', ',
') + ')
,o2 AS(SELECT
*,
' + dbo.StringConcat(' ' + CASE WHEN ParseNumeric = 1 THEN @UtilFuncDbName + 'dbo.TryParseDecimal([' + CNAllINIncluded + ']) AS ' + CNAllOUTIncludedTmp + ''
WHEN ParseDate = 1
THEN 'CAST(' + @UtilFuncDbName + 'dbo.TryParseDateTime([' + CNAllINIncluded + ']) AS ' + DataType + ') AS '
+ CNAllOUTIncludedTmp + ''
WHEN DataType IS NULL THEN '[' + CNAllINIncluded + '] AS ' + CNAllOUTIncludedTmp + ''
ELSE 'CAST([' + CNAllINIncluded + '] AS ' + DataType + ') AS ' + CNAllOUTIncludedTmp + ''
END, ',
') + '
FROM o)
SELECT
' + MAX(ColOut) + CASE WHEN @ListErrors = 1
AND MAX(ErrOut) IS NOT NULL THEN ',
NULLIF(SUBSTRING(' + MAX(ErrOut) + ', 2, 8000), '''') AS ErrorLog'
ELSE ''
END + '
FROM o2'
END + '
GO
' + CASE WHEN @PrintSample = 1 THEN 'SELECT * FROM ' + @DBName + '.' + @FunctionName + '(''SOME,TEST,STRING'')
GO
SELECT b.*
FROM (SELECT ''SOME,TEST,STRING'' AS Input) a
CROSS APPLY ' + @DBName + '.' + @FunctionName + '(Input) b
GO
' ELSE ''
END
FROM s1
CROSS JOIN s2
IF @PrintSQL = 1
EXEC dbo.PrintLargeText
@SQL