Interesting SQL Server behaviour

Hi,
The following code describes a problem I'm having when I'm selecting
data via a Case statement.
The data I'm selecting is from a table with 2 varchar columns and 1
int column. When I select this data I get the following error:
Server: Msg245, Level 16,State 1, Line 1
Syntax error converting the varchar values 'A' to a column of datatype
int.
If I cast the int to a varchar it works, but I would prefer not to
cast if possible. SQL server seems to look at all the datatypes in the
case statement and if a int data type appears, it seems to be trying
to insert into a int column. Is this some sort of temp table? Anyone
know why it assigned an int column here?
Any ideas what I can do
Thanks,
Tom
- --
- -- Set up the table
- --
create table Tom
(charcol1 varchar(2)
,charcol2 varchar(2)
,intcol1 int
)
insert into tom
(charcol1
,intcol1
,charcol2
)
values
('A',1,'C')
- ---
- -- Code
- ---
SELECT
CASE numlist.list
WHEN 1 THEN
Tom.charcol1
WHEN 2 THEN
--cast (Tom.intcol1 as varchar(5))
Tom.intcol1
WHEN 3 THEN
Tom.charcol2
END AS result
FROM Tom
CROSS JOIN (select 1 as list
union all
select 2 as list
union all
select 3 as list
) as numlistThis is how CASE work, result from CASE expression is of the same datatype.
Output is determined as
per "datatype precedence" documented in Books Online.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Staggly" <tomstagg@.gmail.com> wrote in message
news:e646e15f.0502090622.8a26408@.posting.google.com...
> Hi,
> The following code describes a problem I'm having when I'm selecting
> data via a Case statement.
> The data I'm selecting is from a table with 2 varchar columns and 1
> int column. When I select this data I get the following error:
> Server: Msg245, Level 16,State 1, Line 1
> Syntax error converting the varchar values 'A' to a column of datatype
> int.
> If I cast the int to a varchar it works, but I would prefer not to
> cast if possible. SQL server seems to look at all the datatypes in the
> case statement and if a int data type appears, it seems to be trying
> to insert into a int column. Is this some sort of temp table? Anyone
> know why it assigned an int column here?
> Any ideas what I can do
> Thanks,
> Tom
> - --
> - -- Set up the table
> - --
> create table Tom
> (charcol1 varchar(2)
> ,charcol2 varchar(2)
> ,intcol1 int
> )
> insert into tom
> (charcol1
> ,intcol1
> ,charcol2
> )
> values
> ('A',1,'C')
> - ---
> - -- Code
> - ---
> SELECT
> CASE numlist.list
> WHEN 1 THEN
> Tom.charcol1
> WHEN 2 THEN
> --cast (Tom.intcol1 as varchar(5))
> Tom.intcol1
> WHEN 3 THEN
> Tom.charcol2
> END AS result
> FROM Tom
> CROSS JOIN (select 1 as list
> union all
> select 2 as list
> union all
> select 3 as list
> ) as numlist

Intellisense in Views corrupts SQL Syntax

When selecting "Design mode" from "Views", the syntax I enter is
automatically changed on-the-fly and corrupts the syntax.
For example from pubs:
SELECT TOP 100 PERCENT *
FROM dbo.authors a INNER JOIN
dbo.publishers p ON a.city = p.city
ORDER BY a.au_lname DESC
Problems:
1. I can't remove "TOP 100 PERCENT" which is valid SQL.
2. "a INNER JOIN" should be "AS a INNER JOIN"
So, how can I code valid SQL without the code being changed?
Sparky
and the phrase "a INNER JOIN" should be:
"AS a INNER JOIN"which would be valid
EM has a lot of strange things in the query builder. I recommend developing your views in Query
Analyzer. As for TOP, you can't have ORDER BY without TOP in a view. A view is supposed to behave as
a table and a table is not ordered. A view allow ORDER BY if you have TOP as this will influence
which rows are returned, the ordering is possibly still not guaranteed. You might want to look at
this:
http://www.aspfaq.com/show.asp?id=2455
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sparky" <sparky@.boing.com> wrote in message
news:7F7B6BCF-CD0C-405B-8A11-7AC6E268E80F@.microsoft.com...
> When selecting "Design mode" from "Views", the syntax I enter is
> automatically changed on-the-fly and corrupts the syntax.
> For example from pubs:
> SELECT TOP 100 PERCENT *
> FROM dbo.authors a INNER JOIN
> dbo.publishers p ON a.city = p.city
> ORDER BY a.au_lname DESC
> Problems:
> 1. I can't remove "TOP 100 PERCENT" which is valid SQL.
> 2. "a INNER JOIN" should be "AS a INNER JOIN"
> So, how can I code valid SQL without the code being changed?
> Sparky
> and the phrase "a INNER JOIN" should be:
> "AS a INNER JOIN"which would be valid
|||The problem arises when I use Visual Studio 2003's Server Explorer tool to
explore a selected "View" from the sqlserver's treeview. I was not using EM
or QA to edit the view.
Tibor: Thnaks sooo much for the reply - saw your web site -- Great!
Sparky
================================================
"Sparky" wrote:

> When selecting "Design mode" from "Views", the syntax I enter is
> automatically changed on-the-fly and corrupts the syntax.
> For example from pubs:
> SELECT TOP 100 PERCENT *
> FROM dbo.authors a INNER JOIN
> dbo.publishers p ON a.city = p.city
> ORDER BY a.au_lname DESC
> Problems:
> 1. I can't remove "TOP 100 PERCENT" which is valid SQL.
> 2. "a INNER JOIN" should be "AS a INNER JOIN"
> So, how can I code valid SQL without the code being changed?
> Sparky
> and the phrase "a INNER JOIN" should be:
> "AS a INNER JOIN"which would be valid