Default value in SP

Hello group,
I use SP to update value in myTable,
MyTable have a few columns (about 15) and I want update not all field.
Sometime I need update column 2,3,5 and next time 4,5,11.
If parametr is not defined leave current value
My question is how set as default value of parameter equal current value in
table?
My template of procedure (SP)
alter procedure MySP
(
@.idRow int //it is row id and ever is define
@.par1 int,
@.par2 int,
....
@.par15 int,
)
AS
Update MyTable set col1=@.par1, col2=@.par2 ... col15=@.par15 where idRow =
@.idRow
I want call MySP: MySP 2,4,,,,,,,,...,29 or MySP 4,4,1,3,...,34,33,12 or
MySP 1,,,,,...,45
Thx PawelRUPDATE MyTable SET col1 = COALESCE(@.par1, col1), ...
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"PawelR" <pawelratajczak@.poczta.onet.pl> wrote in message
news:%23Q95ibzRFHA.2964@.TK2MSFTNGP15.phx.gbl...
> Hello group,
> I use SP to update value in myTable,
> MyTable have a few columns (about 15) and I want update not all field.
> Sometime I need update column 2,3,5 and next time 4,5,11.
> If parametr is not defined leave current value
> My question is how set as default value of parameter equal current value
> in table?
> My template of procedure (SP)
> alter procedure MySP
> (
> @.idRow int //it is row id and ever is define
> @.par1 int,
> @.par2 int,
> .....
> @.par15 int,
> )
> AS
> Update MyTable set col1=@.par1, col2=@.par2 ... col15=@.par15 where idRow =
> @.idRow
> I want call MySP: MySP 2,4,,,,,,,,...,29 or MySP 4,4,1,3,...,34,33,12 or
> MySP 1,,,,,...,45
>
> Thx PawelR
>|||One option can be to use NULL as the default value and use functions ISNULL
or COALESCE in the UPDATE statement.
alter procedure MySP
(
@.idRow int //it is row id and ever is define
@.par1 int,
@.par2 int = null,
@.par3 int = null,
@.par4 intl,
@.par5 int = null,
....
@.par15 int,
)
AS
set nocount on
Update MyTable
set col1=@.par1, col2=coalesce(@.par2, col2), col3=coalesce(@.par3, col3),
col5=coalesce(@.par5, col5), ... col15=@.par15 where idRow = @.idRow
return @.@.error
go
"PawelR" wrote:

> Hello group,
> I use SP to update value in myTable,
> MyTable have a few columns (about 15) and I want update not all field.
> Sometime I need update column 2,3,5 and next time 4,5,11.
> If parametr is not defined leave current value
> My question is how set as default value of parameter equal current value i
n
> table?
> My template of procedure (SP)
> alter procedure MySP
> (
> @.idRow int //it is row id and ever is define
> @.par1 int,
> @.par2 int,
> .....
> @.par15 int,
> )
> AS
> Update MyTable set col1=@.par1, col2=@.par2 ... col15=@.par15 where idRow =
> @.idRow
> I want call MySP: MySP 2,4,,,,,,,,...,29 or MySP 4,4,1,3,...,34,33,12 or
> MySP 1,,,,,...,45
>
> Thx PawelR
>
>|||let the default value be null, or some other non-applicable value. something
like this:
alter procedure MySP
(
@.idRow int
@.par1 int=null,
@.par2 int=null,
....
@.par15 int,
)
AS
Update MyTable set col1=coalesce(@.par1,col1),
col2=coalesce(@.par2,col2),
..
where idRow = @.idRow
dean
"PawelR" <pawelratajczak@.poczta.onet.pl> wrote in message
news:%23Q95ibzRFHA.2964@.TK2MSFTNGP15.phx.gbl...
> Hello group,
> I use SP to update value in myTable,
> MyTable have a few columns (about 15) and I want update not all field.
> Sometime I need update column 2,3,5 and next time 4,5,11.
> If parametr is not defined leave current value
> My question is how set as default value of parameter equal current value
> in table?
> My template of procedure (SP)
> alter procedure MySP
> (
> @.idRow int //it is row id and ever is define
> @.par1 int,
> @.par2 int,
> .....
> @.par15 int,
> )
> AS
> Update MyTable set col1=@.par1, col2=@.par2 ... col15=@.par15 where idRow =
> @.idRow
> I want call MySP: MySP 2,4,,,,,,,,...,29 or MySP 4,4,1,3,...,34,33,12 or
> MySP 1,,,,,...,45
>
> Thx PawelR
>