[dba-SQLServer] Nulls in sprocs

Steven W. Erbach serbach at new.rr.com
Wed Jan 12 19:18:48 CST 2005


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
> 





More information about the dba-SQLServer mailing list