[dba-SQLServer] Nulls in sprocs

Are you sure the error was with the word convert? Try changing the quotes around the date to single quotes :)

Also, unless you are specifically changing the display format of a given date, I prefer to use CAST over CONVERT.
Its less text to type :)

CAST('1/12/2005' AS DATETIME)

HTH

David McAfee

-----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 5: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