[dba-SQLServer] Nulls in sprocs

It failed in QA (in you first example) because QA doesn't recognize a string enclosed in double quote as text. QA expects single quotes. IF you are passing a text file from VB, VBA, C# or C++ then you must enclose the text in a double quote (unless you are creating the SQL statement in the FE (which technically is a no no, security wise)

I tried this in QA:

DECLARE @SomeDate AS DATETIME
DECLARE @SomeText AS VARCHAR(50)

SET @SomeText = '1/12/2005'
SET @SomeDate = CAST('1/12/2005' AS datetime)

SELECT @someText, @SomeDate, CAST('1/12/2005' AS datetime), CAST(@SomeText AS DATETIME), CONVERT(DATETIME, @SomeText, 101)


and this was my reult:

1/12/2005	2005-01-12 00:00:00.000	2005-01-12 00:00:00.000	2005-01-12 00:00:00.000	2005-01-12 00:00:00.000


-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com]On Behalf Of Steven
W. Erbach
Sent: Thursday, January 13, 2005 4:08 AM
To: dba-sqlserver at databaseadvisors.com
Subject: RE: [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