USE Util
GO
DROP TYPE [dbo].[ColumnDefFixedLength]
GO
CREATE TYPE [dbo].[ColumnDefFixedLength] AS TABLE (Id INT IDENTITY NOT NULL PRIMARY KEY CLUSTERED CHECK (Id BETWEEN 1 AND 1024),
ColumnName VARCHAR(256) NOT NULL UNIQUE NONCLUSTERED,
Included BIT NOT NULL DEFAULT 1,
FixedLength SMALLINT NOT NULL CHECK (FixedLength BETWEEN 1 AND 8000),
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
DROP TYPE [dbo].[ColumnDefDelimited]
GO
CREATE TYPE [dbo].[ColumnDefDelimited] AS TABLE (Id INT IDENTITY NOT NULL PRIMARY KEY CLUSTERED CHECK (Id BETWEEN 1 AND 1024),
ColumnName VARCHAR(256) NOT NULL UNIQUE NONCLUSTERED,
Included BIT NOT NULL DEFAULT 1,
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
DROP TABLE #ColDef
GO
DECLARE @ColDef dbo.ColumnDefFixedLength
INSERT @ColDef (ColumnName, FixedLength, DataType)
SELECT * FROM (VALUES('C1', 30, 'int'), ('C2', 30, 'numeric(5,4)'), ('C3', 40, 'numeric(5,4)')) ddata(ColumnName, FixedLength, DataType)
SELECT * INTO #ColDef FROM @ColDef
DECLARE @FunctionName VARCHAR(256) = 'dbo.TestParse', @Cast BIT = 1
SELECT
'IF OBJECT_ID(''' + @FunctionName + ''') DROP FUNCTION ' + @FunctionName + '
GO
CREATE FUNCTION ' + @FunctionName + '(@Input VARCHAR(MAX))
RETURNS TABLE
AS
RETURN
SELECT
' + Util.dbo.StringConcat(' SUBSTRING(@Input, ' + SStart + ', ' + SLen + ') AS [' + ColumnName + ']','
')
--SELECT *
FROM #ColDef a
CROSS APPLY (SELECT CAST(FixedLength AS VARCHAR) AS SLen,
CASE WHEN 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 DataType IN ('date', 'datetime', 'smalldatetime')
OR DataType LIKE 'datetime2%'
OR DataType LIKE 'time%' THEN 1
ELSE 0
END AS ParseDate) b
CROSS APPLY (SELECT CAST( ISNULL (SUM (FixedLength), 0) + 1 AS VARCHAR) AS SStart FROM #ColDef b WHERE b.iD < A.iD) c