Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
steelkilt
Constraint Violating Yak Guru
255 Posts |
 Posted - 2003-01-02 : 16:31:33
|
| Hi.I need to handle NULLs on my ASP form in the following ways:1. I need to pass a NULL value to my SQL db via an ASP page. I'm using a standard listbox control and so just need to set one of my option values to "NULL". 2. When updating a record from same ASP page, I need to capture blank entries in same listbox and convert to NULLs for passing back to database since the field in question is SMALLINT and so will not accept blank "".thx |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-01-02 : 17:23:05
|
| Take a look at NULLIF in Books Online. It allows you to create a null from a known, fixed value. You could then pass an empty string to your sproc and NULLIF it into a true null:CREATE PROCEDURE GetASPData @param1 varchar(10)='', @param2 int=0 ASINSERT INTO myTable (col1, col2) VALUES (NULLIF(@param1, ''), NULLIF(@param2, 0))That's probably better because you can decide NOT to pass the parameter at all, and it will pick up the default value anyway (of course, you could also default the parameters to null) In any case, you won't have to deal with nulls on the ASP side. |
 |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2003-01-03 : 07:15:58
|
| Rob, I sorta-disagree with you.I think, in general, a dba should strive to write procs that support multiple consumers, including future applications.Playing Jedi mind-tricks with "empty string means null" or "this-or-that means null" has the potential to lead to future entries on a bug list. What if '' is a valid option value at some time in the future? What if the drop downlist is last names ... what do you put for Madonna, Cher or Prince? Or if you decide to pass the string "NULL" as meaning null, what if some poor dudes name is Geraldo Null. These things could happen.Null is null. Empty string is empty string.I say default the param to null and don't pass that param from the application if the value is unknown. Yeah it requires some logic in the ASP, but at the end of the day, I think it makes the interface simpler.Jay White{0} |
|
|
|
steelkilt
Constraint Violating Yak Guru
255 Posts |
Posted - 2003-01-03 : 09:20:16
|
| Thanks for the input. Both views make a lot of sense to me. I've chosen to use NULLIF, but I've changed things on the client side.I need NULLs in the db because my SMALLINT values represent scores that are tallied up via SQL to produce "total" type columns. If a person has not been graded for a particular test, a NULL must be placed in that field, which results in a NULL for the "total". This is acceptable because there is no valid total unless all tests are scored.My workaround is to use zero as the default "has not been tested" option on the ASP form. I then convert this zero to NULL in the SPROC using NULLIF. Why am I doing this? Because it is possible that zero will be accepted soon as the "has not been tested" value -- instead of NULL -- thus allowing a "total" even if some tests were skipped. This possibility is being debated now, until then, I've got my zero value on the ASP form and can remove NULLIF from SPROC quickly.thanks for the input! |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-01-03 : 09:30:56
|
| I probably should've mentioned earlier, it might be better to use -1 or some other completely no-way-in-hell valid number for the replacement value. If zero could potentially be valid in the future, better move to -1 or some other value right now. Same applies for the character data too.I agree with Jay that it is better to be precise and not substitute values for null, but I do remember going through the kind of problems you're seeing now, and it was much easier to have the stored procedure handle the translation than adding the ASP code to numerous pages (especially in the early days before the SP was fully tweaked) |
|
|
|
|
|
|
|
|
pass NULL value to SQL via ASP page