| Author |
Topic |
|
ackweb
Yak Posting Veteran
54 Posts |
 Posted - 2003-09-26 : 01:41:47
|
| I'm using NULLIF() in the following manner to replace blank parameters in my SPROC with NULL values in the SQL UPDATE statement:@Example varchar(50),SETExample = (NULLIF(@Example, '')), While this technique works for string values, it generates a "String was not recognized as a valid DateTime" when using a DateTime data type. The parameter in question is an optional expiration date, which comes from my ASP.NET form as either blank or a valid date string. The following code works when the string is a valid date, but generates the above error when the parameter is blank:@ExpDate smalldatetime,SETExpDate = (NULLIF(@ExpDate, '')), I'd appreciate any suggestion for handling this situation. Thanks! |
|
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2003-09-26 : 02:43:33
|
| If you use something like this:ExpDate = (NULLIF(@ExpDate, 'Date Not Present'))then really, the string 'Date Not Present' can'tbe converted into datetime datatype. |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-09-26 : 07:28:38
|
| there will NEVER be a value that is non-null and non-date if the column is declared as a datetime datatype, so you don't need to perform this on date columns.by definition, datetime datatypes can only hold Nulls (if you allow it) or valid dates. if the column exists in your table and there's data in it, then you don't need to do this.- Jeff |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2003-09-26 : 11:17:25
|
| Philosohpical Question: Why do you prefer a blank to a null?------------------------------------------------------The more you know, the more you know you don't know. |
|
|
|
ackweb
Yak Posting Veteran
54 Posts |
Posted - 2003-09-26 : 13:54:42
|
| I agree that there will "NEVER be a value that is non-null and non-date if the column is declared as a datetime datatype". However, I'm trying to convert the corresponding entry from the Web Form into either a NULL or date value. Since I can't pass a NULL parameter to my SPROC, I'm using NULLIF() to convert a blank value to NULL. With this in mind, I've come up with the following modified code which actually works:@ExpirationDate VarChar(15),SETExpirationDate = (NULLIF(@ExpirationDate, ''))All I've done is change my input parameter from smalldatetime to a varchar() datatype; which can contain an empty string. Based upon the result of the NULLIF() function, I then assign either the valid date string or NULL to the ExpirationDate field (which is a smalldatetime data type). I'd welcome any feedback on this strategy. Thanks! |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-09-26 : 14:45:20
|
| SET ExpirationDate = CASE WHEN isdate(@ExpirationDate) then @ExperationDate ELSE Null END- Jeff |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2003-09-26 : 16:28:43
|
quote:
Originally posted by ackweb
Since I can't pass a NULL parameter to my SPROC...
Ummm... WHY can't you? Do you mean that your sproc rejects it somehow? Many times I have passed a null parameter to a sproc from a web page... In ASP, using the ADODB.Command object, create the parameter and then assign it the value of null by using the VBScript keyword "null". Actually, I would test the value of the item I was going to insert and if it was an empty string, I'd set the parameter to null, otherwise I'd pass in the value.------------------------------------------------------The more you know, the more you know you don't know. |
|
|
|
ackweb
Yak Posting Veteran
54 Posts |
Posted - 2003-09-27 : 00:05:41
|
| When I use Jeff's syntax:SETExample = @Example,ExpirationDate = CASE WHEN ISDATE(@ExpirationDate) THEN @ExpirationDate ELSE NULL END,Example1 = @Example1I get the following error:"Incorrect syntax near keyword 'THEN'.I haven't been able to set NULL values from my web application. Depending upon where in my ASP.NET (C#) code I evaluate the ExpirationDate value and try passing in a NULL value, I'm either unable to instantiate my STRUCT input variable object ("Object reference not set to an instance of an object") or my SPROC ignores the @ExpirationDate input parameter ("Procedure 'spExample' expects parameter '@ExpirationDate', which was not supplied"). If someone knows a way to do this, I'd appreciate some suggestions. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-09-27 : 00:09:32
|
| ooops ... sorry:CASE WHEN ISDATE(@ExpirationDate)=1 THEN @ExpirationDate ELSE NULL END- Jeff |
|
|
|
ackweb
Yak Posting Veteran
54 Posts |
Posted - 2003-09-27 : 00:25:12
|
| I should have caught that as well. Thanks for correcting the syntax on the CASE statement, Jeff. Both of these lines of code yield the same results. Therefore, I'm curious to know whether you use the following SQL functions interchangably or prefer one over the other? If so, why? Thanks again!SET ExpirationDate = (NULLIF(@ExpirationDate, '')),SET ExpirationDate = CASE WHEN ISDATE(@ExpirationDate)=1 THEN @ExpirationDate ELSE NULL END, |
|
|
|
ackweb
Yak Posting Veteran
54 Posts |
Posted - 2003-09-27 : 00:56:18
|
| Just for the record AND to increase my number of posts, I've figured out how to successfully pass NULL values into either my ASP.NET STRUCT or directly into the SPROC. All I needed to do was set a default NULL value for the input parameter in the SPROC.@ExpirationDate smalldatetime = NULL, |
|
|
|
|
NULLIF() and DateTime