bullrout
asked on
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("@ListingImag
plistingImage.Value = ImageContent
myCommand.Parameters.Add(p
'image type
Dim plistingImageType As New SqlParameter("@ListingImag
plistingImageType.Value = strImageType
myCommand.Parameters.Add(p
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("@ListingImag
plistingImage.Value = ImageContent
myCommand.Parameters.Add(p
'image type
Dim plistingImageType As New SqlParameter("@ListingImag
plistingImageType.Value = strImageType
myCommand.Parameters.Add(p
ASKER
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
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
Yes . You can use COALESCE function to doing this.
UPDATE table
SET ImgCol = COALESCE(@ImgParameter, ImgCol )
UPDATE table
SET ImgCol = COALESCE(@ImgParameter, ImgCol )
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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
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
COALESCE is like if statement. it will return first not null value.
ASKER
Sorry to ask so many questions, so your saying that the COALESCE function will return the first NO Null value in the current row?
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)
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)
ASKER
Hi there,
I tried ListingImage = COALESCE(@ListingImage,Nul
Sean
I tried ListingImage = COALESCE(@ListingImage,Nul
Sean
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
So if ListingImage was null it the statement would update the column to null?
ListingImage = COALESCE(@ListingImage,Lis
ListingImage = COALESCE(@ListingImage,Lis
if ListingImage is null and @ListingImage is not null, update ListingImage by @ListingImage.
ASKER
thanks for the help dishanf.
Sean
Sean
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...