title: Dynamically converting all float type columns to decimal
body: <p>I need to convert all float data type columns to decimal with precision in all tables in a particular database.</p>
  
  <p>I have the following code:</p>
  
  <pre><code>DECLARE @sql VARCHAR(8000)
  
  SELECT 
      @sql = COALESCE(@sql + ',', '') + 
             CASE DATA_TYPE 
                WHEN 'float' THEN 'CAST(' + COLUMN_NAME  + ' AS DECIMAL(28, 10))'
                ELSE COLUMN_NAME
             END
  FROM
      INFORMATION_SCHEMA.COLUMNS
  WHERE 
      DATA_TYPE = 'float'
  
  EXEC ('SELECT '+ @sql + ' FROM TABLE_NAME')
  </code></pre>
  
  <p>I also have another version with the decimal scale as a variable:</p>
  
  <pre><code>DECLARE 
      @sql NVARCHAR(MAX),
      @DecimalPlace INT = 10
  
  SELECT
      @sql = COALESCE(@sql + ',', '') + 
             CASE DATA_TYPE 
                WHEN 'float' 
                   THEN 'CAST(' + COLUMN_NAME  + ' AS DECIMAL(28, ' + CAST(@DecimalPlace AS NVARCHAR) + '))'
                   ELSE COLUMN_NAME
             END
  FROM
      INFORMATION_SCHEMA.COLUMNS
  WHERE 
      DATA_TYPE = 'float'
  
  EXEC ('SELECT '+ @sql + ' FROM TABLE_NAME')
  </code></pre>
  
  <p>I am currently getting the following error for my topmost script:</p>
  
  <blockquote>
    <p>Msg 156, Level 15, State 1, Line 1<br>
    Incorrect syntax near the keyword 'from'.</p>
  </blockquote>
  
  <p>I see my issue... 'from TABLE_NAME' is invalid. Here's the SQL generated... </p>
  
  <pre><code>SELECT
      CAST(DWF_ORDERS_EXCHANGE_RATE_DOL AS DECIMAL(28,10)),
      CAST(DWF_ORDERS_ITEM_DISCOUNT_PRC AS DECIMAL(28,10)),
      CAST(DWF_ORDERS_SALES_QTY AS DECIMAL(28,10)),
      CAST(DWF_ORDERS_OPEN_QTY 
  FROM
      TABLE_NAME 
  </code></pre>
  
  <p>It appears as though my logic can only accommodate one table at a time. Is there a relatively easy way to modify my logic in order to accommodate multiple tables fed into a variable?</p>
  
  <p>Additionally, I'm not sure what would be the best practice in order to keep the original float precision/scale if applicable.</p>
  
  <p>Using SQL Server 2014.</p>