Solved

Handling null parameters with stored procedures?

Posted on 2004-08-10
15
1,241 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

791 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