[dba-SQLServer] Nulls in sprocs

Francis,

Hmmm, I'm just wondering what the deal is with QA and the fact that it allowed me to use a date string in quotes instead of CAST or CONVERT. "1/12/2005" was rendered in the table as a proper American-style date.

When I used this EXEC command:

EXEC spUpdPosition 115, "This is the Position", 6, NULL, "This is the Description", "Bob", "Frank", CAST('1/12/2005' AS datetime), 660

I got the error: Incorrect syntax near '1/12/2005'. The sproc looks like this:

CREATE PROCEDURE spUpdPosition (@DeptID int,
@Position nvarchar(50),
@Analyst1 int,
@Analyst2 int,
@Description varchar(8000),
@CreatedBy nvarchar(15),
@UpdatedBy nvarchar(15),
@DateUpdated datetime,
@PositionID int)
AS UPDATE tblPositionData
SET DeptID = @DeptID, Position = @Position, Analyst1 = @Analyst1, Analyst2 = @Analyst2, Description = @Description,
CreatedBy = @CreatedBy, UpdatedBy = @UpdatedBy, DateUpdated = @DateUpdated
WHERE (PositionID = @PositionID)
GO

The DateUpdated is a DateTime field, so I can't figure out why the CAST would fail in QA, while feeding the sproc a string for the date would work.

Steve Erbach
Neenah, WI

> ------------Original Message------------
> From: Francis Harvey <HARVEYF1 at westat.com>
> To: "'dba-sqlserver at databaseadvisors.com'" <dba-sqlserver at databaseadvisors.com>
> Date: Wed, Jan-12-2005 10:26 PM
> Subject: RE: [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