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.

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

Database/Application Documentation

Just general thoughts...
It seems to me that there is a lack of compile time binding between a .NET
application and the a SQL server database.
Can someone suggest the best way to do things like
-identify stored procedures that are no longer being used by an application
-genrerate a call tree of .NET classes and modules which flows all the way
to database SPs.
Is using strongly typed classes, something i am not very familiar with,
helpful?
I've looked at Project Anayzer by Avisio and the output is confusing.
Does Rational have something to help document an application that spans
tiers by analyzing the code?
Is the output quality or quantity?You could run a trace on the server and analyze the output for missing
procedures
http://sqlservercode.blogspot.com/
"Chad" wrote:

> Just general thoughts...
> It seems to me that there is a lack of compile time binding between a .NET
> application and the a SQL server database.
> Can someone suggest the best way to do things like
> -identify stored procedures that are no longer being used by an applicatio
n
> -genrerate a call tree of .NET classes and modules which flows all the way
> to database SPs.
> Is using strongly typed classes, something i am not very familiar with,
> helpful?
> I've looked at Project Anayzer by Avisio and the output is confusing.
> Does Rational have something to help document an application that spans
> tiers by analyzing the code?
> Is the output quality or quantity?
>
>
>|||That is correct. When you compile a .NET application, it only compiles
the managed code. SQL Server objects are considered to be external
resources, and are not compiled or checked in any way. Only the
provider syntax is checked, embedded strings, parameters and SQL
statements are not. There are other Microsoft partners who provide
third-party tools, which you may like better than the one you tried. I
used FMS analyzer a long time ago, which I liked at the time, but I've
not kept up with the available tools. See
http://www.microsoft.com/sql/partners/dbtools.asp for a complete list.
--Mary
On Fri, 30 Sep 2005 23:47:54 -0400, "Chad"
<chad.dokmanovich@.unisys.com> wrote:

>Just general thoughts...
>It seems to me that there is a lack of compile time binding between a .NET
>application and the a SQL server database.
>Can someone suggest the best way to do things like
>-identify stored procedures that are no longer being used by an application
>-genrerate a call tree of .NET classes and modules which flows all the way
>to database SPs.
>Is using strongly typed classes, something i am not very familiar with,
>helpful?
>I've looked at Project Anayzer by Avisio and the output is confusing.
>Does Rational have something to help document an application that spans
>tiers by analyzing the code?
>Is the output quality or quantity?
>
>