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.
ThanksUse 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|||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.|||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')
