Solved

Handling null parameters with stored procedures?

Posted on 2004-08-10
15
1,231 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
Comment Utility
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
Comment Utility
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:dishanf
Comment Utility
Yes . You can use COALESCE function to doing this.

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

0
 
LVL 8

Expert Comment

by:dishanf
Comment Utility
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
Comment Utility
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
Comment Utility
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:dishanf
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 8

Expert Comment

by:dishanf
Comment Utility
COALESCE is like if statement. it will return first not null value.
0
 
LVL 5

Author Comment

by:bullrout
Comment Utility
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:dishanf
Comment Utility
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
Comment Utility
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:dishanf
dishanf earned 250 total points
Comment Utility
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
Comment Utility
So if ListingImage was null it the statement would update the column to null?

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

Expert Comment

by:dishanf
Comment Utility
if ListingImage  is null and @ListingImage is not null, update ListingImage by @ListingImage.
0
 
LVL 5

Author Comment

by:bullrout
Comment Utility
thanks for the help dishanf.

Sean
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

771 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now