Wednesday, December 2, 2009

Create Parameters

view plaincopy to clipboardprint?
  1. DECLARE @TABLE_NAME VARCHAR(50) = 'PrameterTable'  
  2. DECLARE @TableName AS         VARCHAR(200)  
  3. DECLARE @ParamNames AS        VARCHAR(8000)  
  4. SET @TableName = 'TableTest'  
  5. SELECT @ParamNames = (COALESCE(@ParamNames + CHAR(13) + CHAR(10), '') +  
  6.        CASE  
  7.               WHEN DATA_TYPE = 'bigint'  
  8.               THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE  
  9.               WHEN DATA_TYPE = 'binary'  
  10.               THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(character_maximum_length AS VARCHAR) + ')'  
  11.               WHEN DATA_TYPE = 'Bit'  
  12.               THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE  
  13.               WHEN DATA_TYPE = 'Char'  
  14.               THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(character_maximum_length AS VARCHAR) + ')'  
  15.               WHEN DATA_TYPE = 'Date'  
  16.               THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE  
  17.               WHEN DATA_TYPE = 'DateTime'  
  18.               THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE  
  19.               WHEN DATA_TYPE = 'DateTime2'  
  20.               THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE  
  21.               WHEN DATA_TYPE = 'DateTimeOffset'  
  22.               THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE  
  23.               WHEN DATA_TYPE = 'Decimal'  
  24.               THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(NUMERIC_precision AS VARCHAR) + ',' + CAST(numeric_scale AS VARCHAR) + ')'  
  25.               WHEN DATA_TYPE = 'float'  
  26.               THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(NUMERIC_precision AS VARCHAR) + ')'  
  27.               WHEN DATA_TYPE = 'geography'  
  28.               THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE  
  29.               WHEN DATA_TYPE = 'geometry'  
  30.               THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE  
  31.               WHEN DATA_TYPE = 'hierarchyid'  
  32.               THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE  
  33.               WHEN DATA_TYPE = 'image'  
  34.               THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE  
  35.               WHEN DATA_TYPE = 'int'  
  36.               THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE --+ '(' + CAST(NUMERIC_precision AS VARCHAR) + ')'  
  37.               WHEN DATA_TYPE = 'money'  
  38.               THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE --+ '(' + CAST(NUMERIC_precision AS VARCHAR) + ',' + CAST(numeric_scale AS VARCHAR) + ')'  
  39.               WHEN DATA_TYPE = 'NCHAR'  
  40.               THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(character_maximum_length AS VARCHAR) + ')'  
  41.               WHEN DATA_TYPE = 'numeric'  
  42.               THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(NUMERIC_precision AS VARCHAR) + ',' + CAST(numeric_scale AS VARCHAR) + ')'  
  43.               WHEN DATA_TYPE = 'NVARCHAR'  
  44.               THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(character_maximum_length AS VARCHAR) + ')'  
  45.               WHEN DATA_TYPE = 'ntext'  
  46.               THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE --+ '(' + CAST(character_maximum_length AS VARCHAR) + ')'  
  47.               WHEN DATA_TYPE = 'REAL'  
  48.               THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE --+ '(' + CAST(NUMERIC_precision AS VARCHAR) + ')'  
  49.               WHEN DATA_TYPE = 'smalldatetime'  
  50.               THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE  
  51.               WHEN DATA_TYPE = 'smallint'  
  52.               THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE --+ '(' + CAST(NUMERIC_precision AS VARCHAR) + ')'  
  53.               WHEN DATA_TYPE = 'smallmoney'  
  54.               THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE --+ '(' + CAST(NUMERIC_precision AS VARCHAR) + ',' + CAST(numeric_scale AS VARCHAR) + ')'  
  55.               WHEN DATA_TYPE = 'sql_variant'  
  56.               THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE  
  57.               WHEN DATA_TYPE = 'text'  
  58.               THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(character_maximum_length AS VARCHAR) + ')'  
  59.               WHEN DATA_TYPE = 'time'  
  60.               THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE  
  61.               WHEN DATA_TYPE = 'timestamp'  
  62.               THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE  
  63.               WHEN DATA_TYPE = 'tinyint'  
  64.               THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(NUMERIC_precision AS VARCHAR) + ')'  
  65.               WHEN DATA_TYPE = 'uniqueidentifier'  
  66.               THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE  
  67.               WHEN DATA_TYPE = 'varbinary'  
  68.               THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(character_maximum_length AS VARCHAR) + ')'  
  69.               WHEN DATA_TYPE = 'varchar'  
  70.               THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(character_maximum_length AS VARCHAR) + ')'  
  71.               WHEN DATA_TYPE = 'xml'  
  72.               THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE  
  73.        END )           + ','  
  74. FROM   INFORMATION_SCHEMA.Columns  
  75. WHERE  TABLE_NAME = @TableName  
  76.   
  77. SET @ParamNames = LEFT(@ParamNames, LEN(@ParamNames)-1)  
  78. PRINT @ParamNames  


Re Written Query

view plaincopy to clipboardprint?
  1. DECLARE @TABLE_NAME VARCHAR(50) = 'ContractMaster',  
  2. @DATA_TYPE  VARCHAR(50) = 'numeric'  
  3.   
  4. SELECT paremeters       = ('@' + column_name + ' ' +  
  5. CASE  
  6. WHEN data_type   = 'BIGINT'  
  7. OR     data_type = 'Bit'  
  8. OR     data_type = 'Date'  
  9. OR     data_type = 'DateTime'  
  10. OR     data_type = 'DateTime2'  
  11. OR     data_type = 'DateTimeOffset'  
  12. OR     data_type = 'geography'  
  13. OR     data_type = 'geometry'  
  14. OR     data_type = 'hierarchyid'  
  15. OR     data_type = 'image'  
  16. OR     data_type = 'smalldatetime'  
  17. OR     data_type = 'sql_variant'  
  18. OR     data_type = 'time'  
  19. OR     data_type = 'timestamp'  
  20. OR     data_type = 'uniqueidentifier'  
  21. OR     data_type = 'xml'  
  22. THEN data_type  
  23. WHEN data_type   = 'binary'  
  24. OR     data_type = 'Char'  
  25. OR     data_type = 'NCHAR'  
  26. OR     data_type = 'NVARCHAR'  
  27. OR     data_type = 'text'  
  28. OR     data_type = 'varbinary'  
  29. OR     data_type = 'varchar'  
  30. THEN data_type + '(' + CAST(character_maximum_length AS VARCHAR) + ')'  
  31. WHEN data_type   = 'Decimal'  
  32. OR     data_type = 'money'  
  33. OR     data_type = 'numeric'  
  34. OR     data_type = 'smallmoney'  
  35. THEN data_type + '(' + CAST(numeric_precision AS VARCHAR) + ',' + CAST(numeric_scale AS VARCHAR) + ')'  
  36. WHEN data_type   = 'float'  
  37. OR     data_type = 'int'  
  38. OR     data_type = 'REAL'  
  39. OR     data_type = 'smallint'  
  40. OR     data_type = 'tinyint'  
  41. THEN data_type + '(' + CAST(numeric_precision AS VARCHAR) + ')'  
  42. ELSE data_type  
  43. END) + ' ,'  
  44. FROM   information_schema.columns  
  45. WHERE  table_name = @TABLE_NAME  
  46. AND  
  47. (  
  48. CASE  
  49. WHEN  
  50. (  
  51. @DATA_TYPE IS NULL  
  52. OR     @DATA_TYPE       = ''  
  53. )  
  54. THEN 1  
  55. END       = 1  
  56. OR     data_type = @DATA_TYPE  
  57. )  


Re Written Query
view plaincopy to clipboardprint?
  1. DECLARE @TableName AS         VARCHAR(200)  
  2. DECLARE @TableName AS         VARCHAR(200)  
  3. DECLARE @ParamNames AS        VARCHAR(8000)  
  4. DECLARE @HorizontalTabChar AS CHAR(1)  
  5. DECLARE @NewLineChar AS       CHAR(2)  
  6. SET @TableName         = 'TableTest'  
  7. SET @HorizontalTabChar = CHAR(9)  
  8. SET @NewLineChar       = CHAR(13) + CHAR(10)  
  9. SET @ParamNames        = REPLICATE(@HorizontalTabChar, 3)  
  10. SELECT @ParamNames     = @ParamNames + '@' + syscolumns.name + ' ' + t.name + CAST( (  
  11.                      CASE  
  12.                             WHEN COLUMNPROPERTY(OBJECT_ID(@TableName), syscolumns.name, 'Scale') IS NOT NULL  
  13.                             THEN '('  + CAST(COLUMNPROPERTY(OBJECT_ID(@TableName), syscolumns.name, 'Precision') AS VARCHAR) + ',' + CAST(COLUMNPROPERTY(OBJECT_ID(@TableName), syscolumns.name, 'Scale')AS VARCHAR) + ')'  
  14.                             ELSE '('  + CAST( COLUMNPROPERTY(OBJECT_ID(@TableName), syscolumns.name, 'Precision')AS VARCHAR) +')'  
  15.                      END) AS VARCHAR) + ',' + @NewLineChar + REPLICATE(@HorizontalTabChar, 3)  
  16. FROM   sysobjects  
  17.        JOIN syscolumns  
  18.        ON     sysobjects.id = syscolumns.id  
  19.        INNER JOIN systypes t  
  20.        ON     syscolumns.xusertype = t.xusertype  
  21. WHERE  sysobjects.ID               = OBJECT_ID(@TableName)  
  22. SET @ParamNames                    = LEFT(@ParamNames, LEN(@ParamNames)-6)  
  23. PRINT @ParamNames  

Another One Not Much Confident
view plaincopy to clipboardprint?
  1. DECLARE @TABLE_NAME VARCHAR(50)   
  2. SET @TABLE_NAME =  'tabletest'  
  3.   
  4. SELECT '@' + syscolumns.name + ' ' + UPPER(systypes.name) + ' ' +  
  5.        CASE  
  6.               WHEN syscolumns.prec    IS NULL  
  7.               AND    syscolumns.scale IS NULL  
  8.               THEN '(' + CAST(syscolumns.length AS VARCHAR) + ')'  
  9.               WHEN syscolumns.prec    = 0  
  10.               AND    syscolumns.scale = 0  
  11.               THEN '(' + CAST(syscolumns.length AS VARCHAR) + ')'  
  12.               WHEN syscolumns.prec    > 0  
  13.               AND    syscolumns.scale = 0  
  14.               THEN ''--CAST(syscolumns.length AS VARCHAR)  
  15.               WHEN syscolumns.prec          = 0  
  16.               AND    syscolumns.scale IS NULL  
  17.               THEN '(' + CAST(syscolumns.length AS VARCHAR) + ')'  
  18.               WHEN syscolumns.prec          > 0  
  19.               AND    syscolumns.scale IS NULL  
  20.               THEN '(' + CAST(syscolumns.length AS VARCHAR) + ')'  
  21.               ELSE '(' + CAST(syscolumns.prec AS   VARCHAR) + ' ,' + CAST(syscolumns.scale AS VARCHAR) + ')'  
  22.        END  
  23. FROM   sysobjects  
  24.        JOIN syscolumns  
  25.        ON     sysobjects.id = syscolumns.id  
  26.        JOIN systypes  
  27.        ON     syscolumns.xtype=systypes.xtype  
  28. WHERE  sysobjects.xtype       ='U'  
  29. AND    sysobjects.name        = @TABLE_NAME  
Posted by at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)