Problems adding % sign to a number

Hi,
I have a stored procedure which outputs a number as decimal(5,1). I
would like to add a % sign to the end of this number, however when I do
this using CAST(x as VARCHAR)+'%' I get the following error:
Microsoft OLE DB Provider for SQL Server error '80040e07'
Error converting data type varchar to numeric.
The following code works:
SELECT
CASE dbo.ListProfiles.Value
WHEN 0 THEN NULL
ELSE
CAST(((Val1 - Val2)/Val1)*100 AS DECIMAL(5,1))
END AS [Variance],
but when i change it to add the percentage sign (as follows) i get the
above error:
CASE dbo.ListProfiles.Value
WHEN 0 THEN NULL
ELSE
(CAST(
CAST(((Val1-Val2)/Val1)*100 AS DECIMAL(5,1))
AS VARCHAR) + '%')
END AS [Variance],
Can anyone shed any light onto this annoying problem. I have a feeling
that it may just be syntax related, but i've spent ages trying other
things without any success.
Many thanks,
ChrisChris
DECLARE @.dec AS DECIMAL(5,1)
SET @.dec=18.1
SELECT CAST(@.dec AS VARCHAR(10))+'%'
--OR
SELECT CAST(CAST((100-20)/2*100 AS DECIMAL(5,1))AS VARCHAR(10))+'%'
"Chris Dunigan" <chris.dunigan@.agwsha.nhs.uk> wrote in message
news:1113220485.699029.101780@.l41g2000cwc.googlegroups.com...
> Hi,
> I have a stored procedure which outputs a number as decimal(5,1). I
> would like to add a % sign to the end of this number, however when I do
> this using CAST(x as VARCHAR)+'%' I get the following error:
> Microsoft OLE DB Provider for SQL Server error '80040e07'
> Error converting data type varchar to numeric.
> The following code works:
> SELECT
> CASE dbo.ListProfiles.Value
> WHEN 0 THEN NULL
> ELSE
> CAST(((Val1 - Val2)/Val1)*100 AS DECIMAL(5,1))
> END AS [Variance],
> but when i change it to add the percentage sign (as follows) i get the
> above error:
> CASE dbo.ListProfiles.Value
> WHEN 0 THEN NULL
> ELSE
> (CAST(
> CAST(((Val1-Val2)/Val1)*100 AS DECIMAL(5,1))
> AS VARCHAR) + '%')
> END AS [Variance],
> Can anyone shed any light onto this annoying problem. I have a feeling
> that it may just be syntax related, but i've spent ages trying other
> things without any success.
> Many thanks,
> Chris
>|||It's usually best to perform data formatting in application code rather than
in Transact-SQL. That approach is much more robust and scalable.
Hope this helps.
Dan Guzman
SQL Server MVP
"Chris Dunigan" <chris.dunigan@.agwsha.nhs.uk> wrote in message
news:1113220485.699029.101780@.l41g2000cwc.googlegroups.com...
> Hi,
> I have a stored procedure which outputs a number as decimal(5,1). I
> would like to add a % sign to the end of this number, however when I do
> this using CAST(x as VARCHAR)+'%' I get the following error:
> Microsoft OLE DB Provider for SQL Server error '80040e07'
> Error converting data type varchar to numeric.
> The following code works:
> SELECT
> CASE dbo.ListProfiles.Value
> WHEN 0 THEN NULL
> ELSE
> CAST(((Val1 - Val2)/Val1)*100 AS DECIMAL(5,1))
> END AS [Variance],
> but when i change it to add the percentage sign (as follows) i get the
> above error:
> CASE dbo.ListProfiles.Value
> WHEN 0 THEN NULL
> ELSE
> (CAST(
> CAST(((Val1-Val2)/Val1)*100 AS DECIMAL(5,1))
> AS VARCHAR) + '%')
> END AS [Variance],
> Can anyone shed any light onto this annoying problem. I have a feeling
> that it may just be syntax related, but i've spent ages trying other
> things without any success.
> Many thanks,
> Chris
>|||Thanks guys,
nested casts worked a treat
Chris
*** Sent via Developersdex http://www.examnotes.net ***