Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2003
    Posts
    176

    Unanswered: Datatype Conversion during insert

    I am performing an insert inside a stored procedure. In the values list , I am doing some data converision. I am getting an compile error like:

    Server: Msg 170, Level 15, State 1, Procedure premiumstage_to_fact, Line 303
    Line 303: Incorrect syntax near '='.
    The code is:

    INSERT INTO table-name( c1,c2,c3,c4)

    VALUES
    (@v1,
    @v2,
    @variable = CASE WHEN ISDATE([@variable]) <> 1
    THEN 'NULL'
    END
    END AS @variable,
    @v3)

    Where am I going wrong? Where do I do the conversion? The comma after END AS @variable, Is that syntax right?

    Please advise.

    Thanks

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621

    Re: Datatype Conversion during insert

    Use SELECT instead of VALUES.

    INSERT INTO table-name( c1,c2,c3,c4)

    SELECT @v1,
    @v2,
    @variable = CASE WHEN ISDATE([@variable]) <> 1
    THEN 'NULL'
    END
    END AS @variable,
    @v3

  3. #3
    Join Date
    Sep 2003
    Posts
    522
    snail, i don't think your select will work. your syntax attempts to perform a variable assignment which is not allowed in this context. just remove "@variable =" from your select. i would also remove quotes from THEN 'NULL' because i think the true null is intended.

  4. #4
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Originally posted by ms_sql_dba
    snail, i don't think your select will work. your syntax attempts to perform a variable assignment which is not allowed in this context. just remove "@variable =" from your select. i would also remove quotes from THEN 'NULL' because i think the true null is intended.
    to ms_sql_dba:

    You are right - it works for 2000. I am not sure about 7. May somebody test it and reply.


    create table test13(id int,code varchar(10))
    go
    insert test13 values(1,case when 1=1 then 1 else 0 end)
    insert test13 values(1,'4'+'5')

Posting Permissions

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