Concatenation

I am attempting to run the following sql:
select so.name as 'Table_Name', sc.name as 'Col_Name', st.name,
case st.name
when 'varchar' then sc.length
when 'char' then sc.length
when 'numeric' then convert(varchar(10),sc.xprec) + ',' + convert(varchar(10),
sc.xscale)
else sc.length
end as size
from sysobjects so, syscolumns sc, systypes st
where so.xtype = 'U'
and so.name <> 'dtproperties'
and so.id = sc.id
and sc.xtype = st.xtype
order by so.name, sc.name, st.name
I get an error stating:
Syntax error converting the varchar value '18,2' to a column of data type
smallint.
How can I rewrite the concatenation to allow the comma to join the numbers?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200510/1
Robert,
Try:
select so.name as 'Table_Name', sc.name as 'Col_Name', st.name,
case st.name
when 'varchar' then CONVERT(VARCHAR(20),sc.length)
when 'char' then CONVERT(VARCHAR(20),sc.length)
when 'numeric' then convert(varchar(10),sc.xprec) + '.' +
convert(varchar(10),sc.xscale)else CONVERT(VARCHAR(20),sc.length) end as
size
from sysobjects so, syscolumns sc, systypes st
where so.xtype = 'U'
and so.name <> 'dtproperties'
and so.id = sc.id
and sc.xtype = st.xtype
order by so.name, sc.name, st.name
HTH
Jerry
"Robert R via droptable.com" <u3288@.uwe> wrote in message
news:55d586276a600@.uwe...
>I am attempting to run the following sql:
> select so.name as 'Table_Name', sc.name as 'Col_Name', st.name,
> case st.name
> when 'varchar' then sc.length
> when 'char' then sc.length
> when 'numeric' then convert(varchar(10),sc.xprec) + ',' +
> convert(varchar(10),
> sc.xscale)
> else sc.length
> end as size
> from sysobjects so, syscolumns sc, systypes st
> where so.xtype = 'U'
> and so.name <> 'dtproperties'
> and so.id = sc.id
> and sc.xtype = st.xtype
> order by so.name, sc.name, st.name
> I get an error stating:
> Syntax error converting the varchar value '18,2' to a column of data type
> smallint.
> How can I rewrite the concatenation to allow the comma to join the
> numbers?
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200510/1