Determining, then changing, a column's data type
Results 1 to 4 of 4

Thread: Determining, then changing, a column's data type

  1. #1
    Join Date
    Dec 2009
    Posts
    79

    Determining, then changing, a column's data type

    I get SQL Server 2008 table from another department once a month. This table has grown from about 50 or so fields to almost 200.

    Many of these fields get created as FLOAT. This is a result of the download/creation process the department that gives me the table goes through. I have no control over that.

    When the table was pretty small, I would simply go through it and do an ALTER TABLE ALTER COLUMN etc etc and change the type from FLOAT to DECIMAL, which is what I need.

    This has become a bit cumbersome, so I decided to see if I could write a SPROC that would do all of this for me.

    Step number one would be to determine which fields are FLOAT. To do that, I cobbled this together from two or three different posts I found in the internet
    Code:
    SELECT
    clmns.name AS [Name],
    usrt.name AS [DataType],
    ISNULL(baset.name, N'') AS [SystemType]
    FROM <name of my table>
    CAST(
       CASE 
           WHEN baset.precision = 'float') THEN
    'Yes' ELSE 'No'
    END
    I figured once I can pick out those fields, I could add the ALTER TABLE code to change them, then put the whole thing into a SPROC.

    However, Step 1 isn't even working. The code I'm using, as posted above, gives me a "syntax error near the keyword case"

    What am I doing wrong?

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,865

  3. #3
    Join Date
    Dec 2009
    Posts
    79
    Isn't the WHERE handled by the CASE statement?

    If not, then the WHERE is the CASE statement and it would seem to be a bit redundant to add a WHERE that said the same thing.
    Code:
    SELECT
    clmns.name AS [Name],
    usrt.name AS [DataType],
    ISNULL(baset.name, N'') AS [SystemType]
    FROM <name of my table>
    WHERE baset.precision = 'float'
    CAST(
       CASE 
           WHEN baset.precision = 'float') THEN
    'Yes' ELSE 'No'
    END
    That doesn't make sense to me.

    Or, are you saying I don't need the CASE statement at all and I just need a WHERE clause?

  4. #4
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,865
    You can't use CAST without WHERE clause.

    Then what are you doing with CAST?, the right parenthes is within CASE statements, that is not right. The CASE statement will return either Yes or No then what is the query supposed to do with that?.

    The output of CASE in WHERE clause can be used for GROUP BY or to compare with something, you don't have that in the query.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •