<div>
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.<br />
<br />
CREATE PROCEDURE up_test <br />
&nbsp; &nbsp; @param1 int, --required, since there's no default <br />
&nbsp; &nbsp; @param2 varchar(22) = 'n/a', &nbsp;-- not required since there's a default<br />
&nbsp; &nbsp; @param3 varchar(22) = NULL, &nbsp;--null as default<br />
&nbsp; &nbsp; @param3 varchar(22) = '%' &nbsp;--wildcard as default<br />
<br />
AS<br />
&nbsp; &nbsp; SELECT...<br />

</div>


<div>
Hi there,<br />
<br />
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? <br />
<br />
I hope I have explained it properly :)<br />
<br />
Sean 
</div>


<div>
Yes . You can use COALESCE function to doing this.<br />
<br />
UPDATE table<br />
SET ImgCol = &nbsp;COALESCE(@ImgParameter, ImgCol )<br />
<br />

</div>


<div>
COALESCE<br />
Returns the first nonnull expression among its arguments.<br />
<br />
Syntax<br />
COALESCE ( expression [ ,...n ] ) <br />
<br />
Arguments<br />
expression<br />
<br />
Is an expression of any type.<br />
<br />
n<br />
<br />
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.<br />
<br />
Return Types<br />
Returns the same value as expression<br />

</div>


<div>
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.<br />
<br />
dishanf is the syntax for COALESCE the same as using &nbsp; IF NOT EXISTS &nbsp;or would I have to incorporate it into a IF NOT EXISTS(select statement) &nbsp;? I am just trying to get my head around it.<br />
<br />
Sean<br />
<br />

</div>


<div>
create table #test(<br />
id int,<br />
name varchar<br />
)<br />
insert into #test values(1,'a')<br />
insert into #test values(2,'b')<br />
<br />
select * from #test<br />
<br />
declare @nameNew varchar<br />
set @nameNew = 'c'<br />
-- update b -&gt; c<br />
update #test<br />
set name = COALESCE(@nameNew,name) <br />
where id = 2<br />
<br />
select * from #test<br />
<br />
set @nameNew = null<br />
-- update from same column. not by the null<br />
update #test<br />
set name = COALESCE(@nameNew,name) <br />
where id = 2<br />
<br />
select * from #test
</div>


<div>
COALESCE is like if statement. it will return first not null value.<br />

</div>


<div>
Sorry to ask so many questions, so your saying that the COALESCE function will return the first NO Null value in the current row?
</div>


<div>
Its depend on what you put in.<br />
<br />
update #test<br />
set name = COALESCE(@nameNew,name) <br />
where id = 2<br />
<br />
1st this takes @nameNew &nbsp;and check it is null . if not return @nameNew, if null then check name and so on<br />
<br />
execute following queries on QA <br />
SELECT COALESCE(1,null,2)<br />
SELECT COALESCE(null,2,null)<br />
SELECT COALESCE(null,null,3)
</div>


<div>
Hi there,<br />
<br />
I tried ListingImage = COALESCE(@ListingImage,Nul<wbr />l), and I can't seem to get it to work, any ideas?<br />
<br />
Sean
</div>


<div>
So if ListingImage was null it the statement would update the column to null?<br />
<br />
ListingImage = COALESCE(@ListingImage,Lis<wbr />tingImage)<wbr />,<br />

</div>


<div>
if ListingImage &nbsp;is null and @ListingImage is not null, update ListingImage by @ListingImage. 
</div>


<div>
thanks for the help dishanf.<br />
<br />
Sean
</div>


<div>
<div class="content wysiwyg-content">
Hi There,<br />
<br />
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?<br />
<br />
Sean<br />
<br />
<br />
@ListingImage Image,<br />
@ListingImageType Nchar(200),<br />
<br />
!--page code <br />
<br />
'image<br />
Dim plistingImage As New SqlParameter(&quot;@ListingImag<wbr />e&quot;, SqlDbType.Image)<br />
plistingImage.Value = ImageContent<br />
myCommand.Parameters.Add(p<wbr />listingIma<wbr />ge)<br />
'image type<br />
Dim plistingImageType As New SqlParameter(&quot;@ListingImag<wbr />eType&quot;, SqlDbType.Nchar, 200)<br />
plistingImageType.Value = strImageType<br />
myCommand.Parameters.Add(p<wbr />listingIma<wbr />geType)
</div>
</div>


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)

Handling null parameters with stored procedures?

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.