Plz convert NZ() function of access queries to equivalent stored procedure

[shovan mohanty]

Hi,
can anybody convert below access NZ()function(Access Queries) to stored procedure:
nz([t_activity_brand]![price_off_amount2]*[t_activity_brand]![nb_std_bt_planned2],0) AS po_x_nb_bt_planned


Thanks!

shovan

[Jim Doherty]

Hi,
can anybody convert below access NZ()function(Access Queries) to stored procedure:
nz([t_activity_brand]![price_off_amount2]*[t_activity_brand]![nb_std_bt_planned2],0) AS po_x_nb_bt_planned


Thanks!

shovan

You could overall create a generic user defined function in your SQL server that traps the null converts it and returns a zero value output (this way you would wrap your field with the function and be able to return its value as part of your SQL block in a query) It would also be available obviously for use elsewhere within your system

OR

you could use the CASE expression construct to provide the same result within the body of your stored procedure ie:

CASE [price_off_amount2] WHEN IS NULL THEN 0 ELSE [price_off_amount2] END

I'll leave you to adapt this logic to your circumstances

If you get stuck get back to me

Regards

Jim

[FishVal]

Hi, shovan.

As far as I know some dialects of SQL (e.g. TSQL) support COALESCE function.
http://msdn2.microsoft.com/en-us/library/ms190349.aspx