[dba-SQLServer] Nulls in sprocs

Steve,

Yes, CONVERT is recommended. The problem is illustrated with your 1/12/2005
date which could be interpreted as January 12th, 2005 or December 1st, 2005.
I assume you are already aware of this since you chose that for your example
date. However, SQL Server will also make obvious data type conversions for
you which is my typical approach towards dealing with this problem given my
US-centric environment.

I can't really guess how you got your exact syntax error message without the
rest of the query, so I'll just point out that string literals should be in
single quotes unless you have set the QUOTED_IDENTIFIER option to OFF. Maybe
this can explain the change in behavior.

Francis R Harvey III
WB 303, (301)294-3952
harveyf1 at westat.com


> -----Original Message-----
> From: dba-sqlserver-bounces at databaseadvisors.com 
> [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf 
> Of Steven W. Erbach
> Sent: Wednesday, January 12, 2005 8:19 PM
> To: dba-sqlserver at databaseadvisors.com
> Subject: RE: [dba-SQLServer] Nulls in sprocs
> 
> 
> David,
> 
> OK, I tried the QA and inserted NULL in a couple spots, and 
> the query ran all right. At first, though, I had trouble 
> because I thought that I had to use the CONVERT function to 
> change a date string to a datetime:
> 
> CONVERT(datetime, "1/12/2005", 101)
> 
> I ran the Parse Query (Ctrl-F5) on this and got the error:
> 
> Incorrect syntax near the keyword 'CONVERT'.
> 
> So, what the heck, I replaced the function with the date in 
> quotation marks and, voila! She works!
> 
> So what's the deal with CONVERT? I thought that SQL Server 
> REQUIRED the use of CONVERT for queries.
> 
> Steve Erbach
> Neenah, WI
> 
> > ------------Original Message------------
> > From: dmcafee at pacbell.net
> > To: dba-sqlserver at databaseadvisors.com
> > Date: Wed, Jan-12-2005 11:26 AM
> > Subject: RE: [dba-SQLServer] Nulls in sprocs
> > 
> > Have you tried executing a similar example in QA (Query Analyzer)?
> > 
> > Something like:
> > 
> > EXEC spUpdPosition NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 
> > SomeValidTestIDHere
> > 
> > QA, should return the appropriate error.
> > 
> > Have you also looked at the table design in EM or the 
> column view in 
> > the object browser of QA? They will both show you whether 
> or not Nulls 
> > are allowed for each field.
> > 
> > You can also have the sproc return an error to the FE.
> > 
> > Also, in your last post, you mentioned creating the sproc 
> dynamically 
> > in the FE and executing the SQL string. This is generally 
> thought of as 
> > a no-no (a security hole)
> > 
> > HTH
> > David McAfee
> > 
> 
> 
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>