[dba-SQLServer] Nulls in sprocs

David,

As to being sure that the error had to do with CONVERT, I'm pretty sure it did because when I replaced CONVERT(datetime, "1/12/2005", 101) with "1/12/2005" the UPDATE query execute just fine.

Here's my EXEC statement using CAST:

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

I get the error: Incorrect syntax near '1/12/2005'. Beats my pair of jacks.

Here was what worked in QA:

EXEC spUpdPosition 115, "This is the Position", 6, NULL, "This is the Description", "Bob", "Frank", "1/12/2005", 660

Here, again, is my sproc:

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

So, at least in QA, using a date string worked fine. As you can see in the text of the sproc, the DateUpdated field is of type DateTime.

I'm still trying to figure out what's going wrong in my VB.NET code. ADO.NET has an Update method that I use in my VB.NET code in the Transaction. I want to be able to rollback if the commit doesn't work. Where I'm having trouble is just at the point where the Update query is supposed to execute on the server. The .NET Update method is supposed to return the number of rows updated. Every time I get 0 rows. So when the Commit takes place it, of course, succeeds because there are no rows affected.

As far as allowing NULLs in any of the fields in the table, the only fields that are defined as NOT allowing NULLs are the PositionID (an Identity field, and the key) and the DeptID.

I'll keep messing with it. Thanks for keeping up with me on this.

Steve Erbach
Neenah, WI



> ------------Original Message------------
> From: dmcafee at pacbell.net
> To: dba-sqlserver at databaseadvisors.com
> Date: Wed, Jan-12-2005 10:15 PM
> Subject: RE: [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