asked on

SQL Server stored procedure: Not recognized function name

I am in the process of migrating from Access local tables to using a SQL Server back-end database. I was trying to convert SQL strings within my Access application into stored procedures.

How would I convert the following SQL string from my Access application into a stored procedure ?

CREATE PROCEDURE dbo.procTest AS ...

Format(Nz([CashBalance],Nz([Quantity],0)),'#.000') AS [Check/Cert Amount]

I get the error message:

Error 195: 'Nz' is not a recognized function name.

If I remove Nz, I get the error message:

Error 195: 'Format' is not a recognized function name.

Kelvin Sparks

Replace Nz with the SQL Function ISNULL

i.e.ISNULL([CashBalance],0)

Format won't be recognised, you'll need to use either CAST or CONVERT

Kelvin Sparks

Ooops,

Should have given example with CAST

CAST(ISNULL([CashBalance],0) AS decimal(9,3)) as [Check/Cert Amount]

zimmer9

ASKER

How would I rewrite the following SQL statement using ISNULL incorporating both fields CashBalance and Quantity into the SQL statement ?

The field Check/Cert Amount will reflect the value stored in either CashBalance or Quantity depending upon which of these 2 fields contains a value on each record read.

Format(Nz([CashBalance],Nz([Quantity],0)),'#.000') AS [Check/Cert Amount]

ASKER CERTIFIED SOLUTION

Kelvin Sparks

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial