By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,455 Members | 1,979 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,455 IT Pros & Developers. It's quick & easy.

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

P: 2
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
Oct 10 '07 #1
Share this Question
Share on Google+
2 Replies


Jim Doherty
Expert 100+
P: 897
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:

Expand|Select|Wrap|Line Numbers
  1. 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
Oct 10 '07 #2

FishVal
Expert 2.5K+
P: 2,653
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
Oct 10 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.