[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

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