[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 350
  • Last Modified:

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.
0
zimmer9
Asked:
zimmer9
  • 3
1 Solution
 
Kelvin SparksCommented:
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
0
 
Kelvin SparksCommented:
Ooops,

Should have given example with CAST

CAST(ISNULL([CashBalance],0) AS decimal(9,3)) as [Check/Cert Amount]
0
 
zimmer9Author Commented:
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]
0
 
Kelvin SparksCommented:
CAST(ISNULL([CashBalance],ISNULL([Quantity],0)) AS decimal(9,3)) as [Check/Cert Amount]
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now