Stored procedure asking inputs for null columns

Question

I have two tables with primary foreign key relationship. The table in foreign key relation has some columns that can be null (not the fk column) When I run the following sp with all the input values except the columns that can accept the null values (Path1, Path2,...Path5) the compiler throwing error that Path1, Path2...Path5 can not be null.

ALTER PROCEDURE [dbo].[Submit_Product]
    @SubjectId uniqueidentifier,
    -- ... other required parameters ...

    -- optional parameters:
    @Path1 nvarchar(75),
    @Path2 nvarchar(75),
    @Path3 nvarchar(75),
    @Path4 nvarchar(75),
    @Path5 nvarchar(75)
AS
...

Chris Gessler · Accepted Answer · 2012-06-17 19:28:52Z

I'm guessing the error is coming from your application and not from compiling the stored proc?

In any case, you should set defaults in your stored proc like so:

ALTER PROCEDURE [dbo].[Submit_Product]  
    (  
    @SubjectId uniqueidentifier,  
    @City nvarchar(30),  
    @Area nvarchar(20),  
    @Description nvarchar(400),  
    @ContactNo nvarchar(15),  
    @UserId int,  
    /*Fk Table*/  
    @Path1 nvarchar(75) = null,  
    @Path2 nvarchar(75) = null,  
    @Path3 nvarchar(75) = null,  
    @Path4 nvarchar(75) = null,  
    @Path5 nvarchar(75) = null 
    )  
AS

And as a best practice, I recommend specifying column names in your insert statements to avoid future backward compatibility pitfalls...

INSERT INTO dbo.ImagePath (Id, Path1, Path2, Path3, Path4, Path5) 
VALUES (
  @SubjectId, 
  @Path1, 
  @Path2, 
  @Path3, 
  @Path4, 
  @Path5 
)

This way, you can add another column with a default value, and your existing stored procs will not be affected.

JaggenSWE · Accepted Answer · 2012-06-17 19:06:53Z

2

You have to add a default value in the SP for the values that should accept null values.

for example:

@test nvarchar(200) = NULL

Then it would insert a null value if you don't provide any other value.

answered Jun 17, 2012 at 19:06

JaggenSWE

2,1762 gold badges25 silver badges45 bronze badges

Collectives™ on Stack Overflow

Stored procedure asking inputs for null columns

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related