Post reply

Update a sql_variant column

  • April 4, 2022 at 12:44 pm

    Go to Answer

    #4010498

    Trying to update a sql_variant column gets me a conversion error I dont understand.

    Can someone see/tell me, what I am doing wrong...

    --drop table #test

    create table #test (test sql_variant)

    insert into #test values('1234X ')

    SELECT SQL_VARIANT_PROPERTY(test, 'BaseType') from #test

    --varchar

    update t

    set t.test = CASE WHEN SQL_VARIANT_PROPERTY(t.test, 'BaseType') IN ('char', 'varchar', 'nchar', 'nvarchar') THEN 'Hej'

    else 0

    end

    FROM #test t

    --Conversion failed when converting the varchar value 'Hej' to data type int.

    Where does the data type int come from?

    //Naper72

  • April 4, 2022 at 1:21 pm

    Answer

    #4010511

    "else 0"

    Try changing 0 (zero) to '0' or some other string

  • April 4, 2022 at 2:25 pm

    #4010544

    mattias.palm1 wrote:

    Where does the data type int come from?

    //Naper72

    It comes from the "ELSE 0", as ratbak implied.  The "0" by itself is a numeric.  Use "'0'" instead.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply