[dba-SQLServer] Nulls in sprocs

You don't say what you are using for an FE but I know both Access and VB
expect every parameter to be accounted for.  Try setting default values.

Doris Manning
Database Administrator
Hargrove Inc.
www.hargroveinc.com


-----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 8:51 AM
To: dba-sqlserver at databaseadvisors.com
Subject: [dba-SQLServer] Nulls in sprocs


Dear Group,

I've run into a problem with UPDATE stored procedures that puzzles me. I
have an sproc that 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 PositionID is the key field, and it has the Identity property set to
yes.

My problem is this: if one of the fields to be updated is NULL, it seems
that the sproc won't execute. I say "seems" because I get no indication back
from SQL Server as to WHY the sproc failed. If there's a value for every
field then it works fine, so I concluded that the problems arise from NULL
values being written to the table.

Can anyone tell me how to modify the sproc to account for possible NULL
values in any of the fields? The only fields that have been defined as
disallowing NULLs are the PositionID itself (automatically generated) and
the DeptID, the foreign key.

Regards,
 
Steve Erbach
Scientific Marketing
Neenah, WI




_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com