Handling null parameters with stored procedures?

Hi There,

I was wondering if it was possible to have null parameters when executing a stored procedure? I have a form that submits data to SQL Server and one of the fields is an image field and I want the user to be able to leave this field blank if possible. Can I cater for this condition?

Sean


@ListingImage Image,
@ListingImageType Nchar(200),

!--page code

'image
Dim plistingImage As New SqlParameter("@ListingImage", SqlDbType.Image)
plistingImage.Value = ImageContent
myCommand.Parameters.Add(plistingImage)
'image type
Dim plistingImageType As New SqlParameter("@ListingImageType", SqlDbType.Nchar, 200)
plistingImageType.Value = strImageType
myCommand.Parameters.Add(plistingImageType)
LVL 5
bullroutAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
jdlambert1Connect With a Mentor Commented:
I think I understand what you want, and you'll also need to pass in a parameter to identify the user. Here's a sample of how to do it. If it's not what you want, perhaps it will help you to understand what else to ask.

CREATE PROCEDURE up_IU_TestTable
  @directive char(1) = Null,  --variable IS NOT required
  @user_id varchar(10),  --variable IS required (change the datatype as necessary)
  @ListingImage image, --variable IS required
  @ListingImageType NChar(200) --variable IS required
AS

IF @Directive = 'I'
  BEGIN
    INSERT TestTable(UserID, ListingImage, ListingImageType) VALUES(@user_id, @ListingImage, @ListingImageType)
  END
ELSE
  IF @directive = 'U'
    BEGIN
      UPDATE TestTable SET ListingImage = @ListingImage, ListingImageType = @ListingImageType WHERE UserID = @user_id
    END
  ELSE
    BEGIN
      --code to execute when @directive is Null
      IF NOT EXISTS (SELECT UserID FROM TestTable WHERE UserID = @user_id AND ListingImage = @ListingImage AND ListingImageType = @ListingImageType)
        INSERT TestTable(UserID, ListingImage, ListingImageType) VALUES(@user_id, @ListingImage, @ListingImageType)
    END
0
 
jdlambert1Commented:
Sure, just give the procedure's input variable a default (even NULL). The defaults shown below are only used if no value is passed in for that variable.

CREATE PROCEDURE up_test
    @param1 int, --required, since there's no default
    @param2 varchar(22) = 'n/a',  -- not required since there's a default
    @param3 varchar(22) = NULL,  --null as default
    @param3 varchar(22) = '%'  --wildcard as default

AS
    SELECT...
0
 
bullroutAuthor Commented:
Hi there,

The procedure has 2 different functions, I pass in the parameter @directive which can = I or U (insert) or (update), If i have the default value of the parameter as null on the update then the value of the field will become null. I would like to if possible to make it so that on the update statment if the user has not selected a new image file and the DB field already has an image stored in it, the stement does not insert a null value? Is this possible may be with a select statement into a variable?

I hope I have explained it properly :)

Sean
0
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

 
Dishan FernandoSoftware Engineer / DBACommented:
Yes . You can use COALESCE function to doing this.

UPDATE table
SET ImgCol =  COALESCE(@ImgParameter, ImgCol )

0
 
Dishan FernandoSoftware Engineer / DBACommented:
COALESCE
Returns the first nonnull expression among its arguments.

Syntax
COALESCE ( expression [ ,...n ] )

Arguments
expression

Is an expression of any type.

n

Is a placeholder indicating that multiple expressions can be specified. All expressions must be of the same type or must be implicitly convertible to the same type.

Return Types
Returns the same value as expression
0
 
bullroutAuthor Commented:
hi there thank for the quick answers, I understand the solution that was put forward by jdlambert1, but the one dishanf I don't really understand too well.

dishanf is the syntax for COALESCE the same as using   IF NOT EXISTS  or would I have to incorporate it into a IF NOT EXISTS(select statement)  ? I am just trying to get my head around it.

Sean

0
 
Dishan FernandoSoftware Engineer / DBACommented:
create table #test(
id int,
name varchar
)
insert into #test values(1,'a')
insert into #test values(2,'b')

select * from #test

declare @nameNew varchar
set @nameNew = 'c'
-- update b -> c
update #test
set name = COALESCE(@nameNew,name)
where id = 2

select * from #test

set @nameNew = null
-- update from same column. not by the null
update #test
set name = COALESCE(@nameNew,name)
where id = 2

select * from #test
0
 
Dishan FernandoSoftware Engineer / DBACommented:
COALESCE is like if statement. it will return first not null value.
0
 
bullroutAuthor Commented:
Sorry to ask so many questions, so your saying that the COALESCE function will return the first NO Null value in the current row?
0
 
Dishan FernandoSoftware Engineer / DBACommented:
Its depend on what you put in.

update #test
set name = COALESCE(@nameNew,name)
where id = 2

1st this takes @nameNew  and check it is null . if not return @nameNew, if null then check name and so on

execute following queries on QA
SELECT COALESCE(1,null,2)
SELECT COALESCE(null,2,null)
SELECT COALESCE(null,null,3)
0
 
bullroutAuthor Commented:
Hi there,

I tried ListingImage = COALESCE(@ListingImage,Null), and I can't seem to get it to work, any ideas?

Sean
0
 
Dishan FernandoConnect With a Mentor Software Engineer / DBACommented:
ListingImage = COALESCE(@ListingImage,ListingImage),

if @ListingImage is not null
update ListingImage by @ListingImage
else update ListingImage by ListingImage
0
 
bullroutAuthor Commented:
So if ListingImage was null it the statement would update the column to null?

ListingImage = COALESCE(@ListingImage,ListingImage),
0
 
Dishan FernandoSoftware Engineer / DBACommented:
if ListingImage  is null and @ListingImage is not null, update ListingImage by @ListingImage.
0
 
bullroutAuthor Commented:
thanks for the help dishanf.

Sean
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.