need help- case...when not working

I am getting the error
Incorrect syntax near 'fValue1_precision'.
from the stored procedure below
Can someone see what is wrong?
Thanks
...
@.MyField3 float(8)=NULL,
@.MyField3_precision int=NULL,
...
SELECT
MyTable.[ID],
MyTable.MyField1,
MyTable.MyField2,
(CASE
WHEN MyField3_precision IS NULL THEN MyField3
ELSE CONVERT(decimal(10, MyField3_precision), MyField3)
END) AS MyField3,
MyTable.MyField4,
MyTable.MyField5,Mad Scientist Jr wrote:
> I am getting the error
> Incorrect syntax near 'fValue1_precision'.
> from the stored procedure below
> Can someone see what is wrong?
> Thanks
> ...
> @.MyField3 float(8)=NULL,
> @.MyField3_precision int=NULL,
> ...
> SELECT
> MyTable.[ID],
> MyTable.MyField1,
> MyTable.MyField2,
> (CASE
> WHEN MyField3_precision IS NULL THEN MyField3
> ELSE CONVERT(decimal(10, MyField3_precision), MyField3)
> END) AS MyField3,
> MyTable.MyField4,
> MyTable.MyField5,
Given that the code fragment you posted doesn't include the name
referred to in the error message it's kind of hard to be sure...
However, this bit isn't valid syntax:
CONVERT(decimal(10, MyField3_precision)
You can't use a column value for the precision. The precision must be a
literal value.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||the actual error was
Incorrect syntax near 'MyField3_precision'.
but that sounds like it could be the problem
thanks|||so the correct way to do it would be something like this?
(CASE
WHEN MyField3_precision IS NULL THEN MyField3
WHEN MyField3_precision=0 THEN CONVERT(decimal(10, 0),
MyField3)
--WHEN MyField3_precision=0 THEN CONVERT(int, MyField3)
WHEN MyField3_precision=1 THEN CONVERT(decimal(10, 1),
MyField3)
WHEN MyField3_precision=2 THEN CONVERT(decimal(10, 2),
MyField3)
WHEN MyField3_precision=3 THEN CONVERT(decimal(10, 3),
MyField3)
WHEN MyField3_precision=4 THEN CONVERT(decimal(10, 4),
MyField3)
WHEN MyField3_precision=5 THEN CONVERT(decimal(10, 5),
MyField3)
WHEN MyField3_precision=6 THEN CONVERT(decimal(10, 6),
MyField3)
WHEN MyField3_precision=7 THEN CONVERT(decimal(10, 7),
MyField3)
WHEN MyField3_precision=8 THEN CONVERT(decimal(10, 8),
MyField3)
WHEN MyField3_precision=9 THEN CONVERT(decimal(10, 9),
MyField3)
WHEN MyField3_precision=10 THEN CONVERT(decimal(10, 10),
MyField3)
ELSE MyField3
END) AS MyField3,|||Mad Scientist Jr wrote:
> so the correct way to do it would be something like this?
> (CASE
> WHEN MyField3_precision IS NULL THEN MyField3
> WHEN MyField3_precision=0 THEN CONVERT(decimal(10, 0),
> MyField3)
> --WHEN MyField3_precision=0 THEN CONVERT(int, MyField3)
> WHEN MyField3_precision=1 THEN CONVERT(decimal(10, 1),
> MyField3)
> WHEN MyField3_precision=2 THEN CONVERT(decimal(10, 2),
> MyField3)
> WHEN MyField3_precision=3 THEN CONVERT(decimal(10, 3),
> MyField3)
> WHEN MyField3_precision=4 THEN CONVERT(decimal(10, 4),
> MyField3)
> WHEN MyField3_precision=5 THEN CONVERT(decimal(10, 5),
> MyField3)
> WHEN MyField3_precision=6 THEN CONVERT(decimal(10, 6),
> MyField3)
> WHEN MyField3_precision=7 THEN CONVERT(decimal(10, 7),
> MyField3)
> WHEN MyField3_precision=8 THEN CONVERT(decimal(10, 8),
> MyField3)
> WHEN MyField3_precision=9 THEN CONVERT(decimal(10, 9),
> MyField3)
> WHEN MyField3_precision=10 THEN CONVERT(decimal(10, 10),
> MyField3)
> ELSE MyField3
> END) AS MyField3,
No. A column can only have one type, scale and precision and that's
determined at compile-time, so the multiple versions of CONVERT aren't
doing much for you. If you want each row formatted differently in SQL
then you'll have to convert the values to strings. Probably makes more
sense to return numbers at full precision and then format them in the
client app or presentation tier.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Strange, the above code is working. Using case-when I am getting back
the precision I want in the select (the result is being displayed in a
datagrid). It appears to be working, so what is wrong with the code?
Thanks|||Mad Scientist Jr wrote:
> Strange, the above code is working. Using case-when I am getting back
> the precision I want in the select (the result is being displayed in a
> datagrid). It appears to be working, so what is wrong with the code?
> Thanks
Depends what you mean by "appears to be working". What is it supposed
to do? Formatting of numbers is controlled by your client application,
not by SQL Server. Possibly the implict rounding performed by CONVERT
is influencing the way your app displays the numbers - but you don't
need CONVERT to do rounding. You can use ROUND or FLOOR instead.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Back to basics. CASE is an expression. An expression returns a scalar
result of one and only one data type. Why? Because a column can have
one and only one data type!! You missed the very definition of 1NF.
You might also way to learn HUGE differences between columns and
fields. Fields can change types and structure because in a file
system, the host program gives meanng to the data as it reads it from
left to right. In a RDBMS, the meaning is in the schema, not the
applications.