DECLARE @TABLE_NAME VARCHAR(50) = 'PrameterTable' DECLARE @TableName AS VARCHAR(200) DECLARE @ParamNames AS VARCHAR(8000) SET @TableName = 'TableTest' SELECT @ParamNames = (COALESCE(@ParamNames + CHAR(13) + CHAR(10), '') + CASE WHEN DATA_TYPE = 'bigint' THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE WHEN DATA_TYPE = 'binary' THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(character_maximum_length AS VARCHAR) + ')' WHEN DATA_TYPE = 'Bit' THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE WHEN DATA_TYPE = 'Char' THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(character_maximum_length AS VARCHAR) + ')' WHEN DATA_TYPE = 'Date' THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE WHEN DATA_TYPE = 'DateTime' THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE WHEN DATA_TYPE = 'DateTime2' THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE WHEN DATA_TYPE = 'DateTimeOffset' THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE WHEN DATA_TYPE = 'Decimal' THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(NUMERIC_precision AS VARCHAR) + ',' + CAST(numeric_scale AS VARCHAR) + ')' WHEN DATA_TYPE = 'float' THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(NUMERIC_precision AS VARCHAR) + ')' WHEN DATA_TYPE = 'geography' THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE WHEN DATA_TYPE = 'geometry' THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE WHEN DATA_TYPE = 'hierarchyid' THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE WHEN DATA_TYPE = 'image' THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE WHEN DATA_TYPE = 'int' THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE --+ '(' + CAST(NUMERIC_precision AS VARCHAR) + ')' WHEN DATA_TYPE = 'money' THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE --+ '(' + CAST(NUMERIC_precision AS VARCHAR) + ',' + CAST(numeric_scale AS VARCHAR) + ')' WHEN DATA_TYPE = 'NCHAR' THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(character_maximum_length AS VARCHAR) + ')' WHEN DATA_TYPE = 'numeric' THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(NUMERIC_precision AS VARCHAR) + ',' + CAST(numeric_scale AS VARCHAR) + ')' WHEN DATA_TYPE = 'NVARCHAR' THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(character_maximum_length AS VARCHAR) + ')' WHEN DATA_TYPE = 'ntext' THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE --+ '(' + CAST(character_maximum_length AS VARCHAR) + ')' WHEN DATA_TYPE = 'REAL' THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE --+ '(' + CAST(NUMERIC_precision AS VARCHAR) + ')' WHEN DATA_TYPE = 'smalldatetime' THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE WHEN DATA_TYPE = 'smallint' THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE --+ '(' + CAST(NUMERIC_precision AS VARCHAR) + ')' WHEN DATA_TYPE = 'smallmoney' THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE --+ '(' + CAST(NUMERIC_precision AS VARCHAR) + ',' + CAST(numeric_scale AS VARCHAR) + ')' WHEN DATA_TYPE = 'sql_variant' THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE WHEN DATA_TYPE = 'text' THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(character_maximum_length AS VARCHAR) + ')' WHEN DATA_TYPE = 'time' THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE WHEN DATA_TYPE = 'timestamp' THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE WHEN DATA_TYPE = 'tinyint' THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(NUMERIC_precision AS VARCHAR) + ')' WHEN DATA_TYPE = 'uniqueidentifier' THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE WHEN DATA_TYPE = 'varbinary' THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(character_maximum_length AS VARCHAR) + ')' WHEN DATA_TYPE = 'varchar' THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(character_maximum_length AS VARCHAR) + ')' WHEN DATA_TYPE = 'xml' THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE END ) + ',' FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = @TableName SET @ParamNames = LEFT(@ParamNames, LEN(@ParamNames)-1) PRINT @ParamNames
Re Written Query
DECLARE @TABLE_NAME VARCHAR(50) = 'ContractMaster', @DATA_TYPE VARCHAR(50) = 'numeric' SELECT paremeters = ('@' + column_name + ' ' + CASE WHEN data_type = 'BIGINT' OR data_type = 'Bit' OR data_type = 'Date' OR data_type = 'DateTime' OR data_type = 'DateTime2' OR data_type = 'DateTimeOffset' OR data_type = 'geography' OR data_type = 'geometry' OR data_type = 'hierarchyid' OR data_type = 'image' OR data_type = 'smalldatetime' OR data_type = 'sql_variant' OR data_type = 'time' OR data_type = 'timestamp' OR data_type = 'uniqueidentifier' OR data_type = 'xml' THEN data_type WHEN data_type = 'binary' OR data_type = 'Char' OR data_type = 'NCHAR' OR data_type = 'NVARCHAR' OR data_type = 'text' OR data_type = 'varbinary' OR data_type = 'varchar' THEN data_type + '(' + CAST(character_maximum_length AS VARCHAR) + ')' WHEN data_type = 'Decimal' OR data_type = 'money' OR data_type = 'numeric' OR data_type = 'smallmoney' THEN data_type + '(' + CAST(numeric_precision AS VARCHAR) + ',' + CAST(numeric_scale AS VARCHAR) + ')' WHEN data_type = 'float' OR data_type = 'int' OR data_type = 'REAL' OR data_type = 'smallint' OR data_type = 'tinyint' THEN data_type + '(' + CAST(numeric_precision AS VARCHAR) + ')' ELSE data_type END) + ' ,' FROM information_schema.columns WHERE table_name = @TABLE_NAME AND ( CASE WHEN ( @DATA_TYPE IS NULL OR @DATA_TYPE = '' ) THEN 1 END = 1 OR data_type = @DATA_TYPE )
Re Written Query
DECLARE @TableName AS VARCHAR(200) DECLARE @TableName AS VARCHAR(200) DECLARE @ParamNames AS VARCHAR(8000) DECLARE @HorizontalTabChar AS CHAR(1) DECLARE @NewLineChar AS CHAR(2) SET @TableName = 'TableTest' SET @HorizontalTabChar = CHAR(9) SET @NewLineChar = CHAR(13) + CHAR(10) SET @ParamNames = REPLICATE(@HorizontalTabChar, 3) SELECT @ParamNames = @ParamNames + '@' + syscolumns.name + ' ' + t.name + CAST( ( CASE WHEN COLUMNPROPERTY(OBJECT_ID(@TableName), syscolumns.name, 'Scale') IS NOT NULL THEN '(' + CAST(COLUMNPROPERTY(OBJECT_ID(@TableName), syscolumns.name, 'Precision') AS VARCHAR) + ',' + CAST(COLUMNPROPERTY(OBJECT_ID(@TableName), syscolumns.name, 'Scale')AS VARCHAR) + ')' ELSE '(' + CAST( COLUMNPROPERTY(OBJECT_ID(@TableName), syscolumns.name, 'Precision')AS VARCHAR) +')' END) AS VARCHAR) + ',' + @NewLineChar + REPLICATE(@HorizontalTabChar, 3) FROM sysobjects JOIN syscolumns ON sysobjects.id = syscolumns.id INNER JOIN systypes t ON syscolumns.xusertype = t.xusertype WHERE sysobjects.ID = OBJECT_ID(@TableName) SET @ParamNames = LEFT(@ParamNames, LEN(@ParamNames)-6) PRINT @ParamNames
Another One Not Much Confident
DECLARE @TABLE_NAME VARCHAR(50) SET @TABLE_NAME = 'tabletest' SELECT '@' + syscolumns.name + ' ' + UPPER(systypes.name) + ' ' + CASE WHEN syscolumns.prec IS NULL AND syscolumns.scale IS NULL THEN '(' + CAST(syscolumns.length AS VARCHAR) + ')' WHEN syscolumns.prec = 0 AND syscolumns.scale = 0 THEN '(' + CAST(syscolumns.length AS VARCHAR) + ')' WHEN syscolumns.prec > 0 AND syscolumns.scale = 0 THEN ''--CAST(syscolumns.length AS VARCHAR) WHEN syscolumns.prec = 0 AND syscolumns.scale IS NULL THEN '(' + CAST(syscolumns.length AS VARCHAR) + ')' WHEN syscolumns.prec > 0 AND syscolumns.scale IS NULL THEN '(' + CAST(syscolumns.length AS VARCHAR) + ')' ELSE '(' + CAST(syscolumns.prec AS VARCHAR) + ' ,' + CAST(syscolumns.scale AS VARCHAR) + ')' END FROM sysobjects JOIN syscolumns ON sysobjects.id = syscolumns.id JOIN systypes ON syscolumns.xtype=systypes.xtype WHERE sysobjects.xtype ='U' AND sysobjects.name = @TABLE_NAME
No comments:
Post a Comment