Solved

Handling null parameters with stored procedures?

Posted on 2004-08-10
15
1,242 Views
Last Modified: 2008-02-01
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)
0
Comment
Question by:bullrout
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 2
15 Comments
 
LVL 15

Expert Comment

by:jdlambert1
ID: 11769270
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
 
LVL 5

Author Comment

by:bullrout
ID: 11769333
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
 
LVL 8

Expert Comment

by:Dishan Fernando
ID: 11769429
Yes . You can use COALESCE function to doing this.

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

0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 8

Expert Comment

by:Dishan Fernando
ID: 11769451
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
 
LVL 15

Accepted Solution

by:
jdlambert1 earned 250 total points
ID: 11769475
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
 
LVL 5

Author Comment

by:bullrout
ID: 11769703
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
 
LVL 8

Expert Comment

by:Dishan Fernando
ID: 11770152
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
 
LVL 8

Expert Comment

by:Dishan Fernando
ID: 11770163
COALESCE is like if statement. it will return first not null value.
0
 
LVL 5

Author Comment

by:bullrout
ID: 11770467
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
 
LVL 8

Expert Comment

by:Dishan Fernando
ID: 11770530
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
 
LVL 5

Author Comment

by:bullrout
ID: 11771861
Hi there,

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

Sean
0
 
LVL 8

Assisted Solution

by:Dishan Fernando
Dishan Fernando earned 250 total points
ID: 11780751
ListingImage = COALESCE(@ListingImage,ListingImage),

if @ListingImage is not null
update ListingImage by @ListingImage
else update ListingImage by ListingImage
0
 
LVL 5

Author Comment

by:bullrout
ID: 11789012
So if ListingImage was null it the statement would update the column to null?

ListingImage = COALESCE(@ListingImage,ListingImage),
0
 
LVL 8

Expert Comment

by:Dishan Fernando
ID: 11790139
if ListingImage  is null and @ListingImage is not null, update ListingImage by @ListingImage.
0
 
LVL 5

Author Comment

by:bullrout
ID: 11827262
thanks for the help dishanf.

Sean
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question