"Scott A. Keen" wrote:

> Hi, I need a little help writing the code to conditionally update field(s)
> if the parameter value passed in is not null.
>
> Here's my first stab at doing this, but it's very lengthy. I have to write
> the snippet of code for every parameter I pass in and I've go over 50+
> parameters of differenty types (bit, int, smalldatetime, uniqueidentifier,
> varchar). The purpose of checking if the parameter is Null is that I do not
> want to overwrite existing data with a Null value if the parameter value
> passed in is Null.
>
> Please advise if there's a simpler way of doing this.
>
> Thanks,
>
> Scott
>
>
>
> CREATE PROCEDURE [update_OrderFromDataImport]
>  (
>  @Order_GUID    uniqueidentifier,
>  @Order_ActualArrivalDate smalldatetime,
>  @Order_ActualShipDate  smalldatetime,
>  @Order_BOL   varchar(50)
>  )
> AS
> IF @Order_ActualArrivalDate <> Null
>    BEGIN
>    BEGIN TRANSACTION
>    UPDATE [psOrder]
>    SET [ActualArrivalDate] = @Order_ActualArrivalDate
>    WHERE [GUID]  = @Order_GUID
>    COMMIT TRANSACTION
>    END
>
> IF @Order_ActualShipDate <> Null
>    BEGIN
>    BEGIN TRANSACTION
>    UPDATE [psOrder]
>    SET [ActualShipDate] = @Order_ActualShipDate
>    WHERE [GUID]  = @Order_GUID
>    COMMIT TRANSACTION
>    END
>
> IF @Order_BOL <> Null
>    BEGIN
>    BEGIN TRANSACTION
>    UPDATE [psOrder]
>    SET [BOL] = @Order_BOL
>    WHERE [GUID]  = @Order_GUID
>    COMMIT TRANSACTION
>    END
>
>
>

"Sha Anand" <ShaAn***@discussions.microsoft.com> wrote in message
news:87F86588-3057-4764-A20C-5ACDBD0BC373@microsoft.com...
> You can try something like this
>
> UPDATE Table1 SET field1 = ISNULL(@parField1,field1
> ),field2=ISNULL(@parField2,field2).....
>
> - Sha Anand
>
> "Scott A. Keen" wrote:
>
> > Hi, I need a little help writing the code to conditionally update
field(s)
> > if the parameter value passed in is not null.
> >
> > Here's my first stab at doing this, but it's very lengthy. I have to
write
> > the snippet of code for every parameter I pass in and I've go over 50+
> > parameters of differenty types (bit, int, smalldatetime,
uniqueidentifier,
> > varchar). The purpose of checking if the parameter is Null is that I do
not
> > want to overwrite existing data with a Null value if the parameter value
> > passed in is Null.
> >
> > Please advise if there's a simpler way of doing this.
> >
> > Thanks,
> >
> > Scott
> >
> >
> >
> > CREATE PROCEDURE [update_OrderFromDataImport]
> >  (
> >  @Order_GUID    uniqueidentifier,
> >  @Order_ActualArrivalDate smalldatetime,
> >  @Order_ActualShipDate  smalldatetime,
> >  @Order_BOL   varchar(50)
> >  )
> > AS
> > IF @Order_ActualArrivalDate <> Null
> >    BEGIN
> >    BEGIN TRANSACTION
> >    UPDATE [psOrder]
> >    SET [ActualArrivalDate] = @Order_ActualArrivalDate
> >    WHERE [GUID]  = @Order_GUID
> >    COMMIT TRANSACTION
> >    END
> >
> > IF @Order_ActualShipDate <> Null
> >    BEGIN
> >    BEGIN TRANSACTION
> >    UPDATE [psOrder]
> >    SET [ActualShipDate] = @Order_ActualShipDate
> >    WHERE [GUID]  = @Order_GUID
> >    COMMIT TRANSACTION
> >    END
> >
> > IF @Order_BOL <> Null
> >    BEGIN
> >    BEGIN TRANSACTION
> >    UPDATE [psOrder]
> >    SET [BOL] = @Order_BOL
> >    WHERE [GUID]  = @Order_GUID
> >    COMMIT TRANSACTION
> >    END
> >
> >
> >

> Thank you. That should work for null values.
>
> If I also want to do the same condition for parameters which are empty
> strings, is there way to do this?
>
> I looked for an ISEMPTY function but there doesn't appear to be a T-SQL
> function for this.
>
>
>
>
> "Sha Anand" <ShaAn***@discussions.microsoft.com> wrote in message
> news:87F86588-3057-4764-A20C-5ACDBD0BC373@microsoft.com...
> > You can try something like this
> >
> > UPDATE Table1 SET field1 = ISNULL(@parField1,field1
> > ),field2=ISNULL(@parField2,field2).....
> >
> > - Sha Anand
> >
> > "Scott A. Keen" wrote:
> >
> > > Hi, I need a little help writing the code to conditionally update
> field(s)
> > > if the parameter value passed in is not null.
> > >
> > > Here's my first stab at doing this, but it's very lengthy. I have to
> write
> > > the snippet of code for every parameter I pass in and I've go over 50+
> > > parameters of differenty types (bit, int, smalldatetime,
> uniqueidentifier,
> > > varchar). The purpose of checking if the parameter is Null is that I do
> not
> > > want to overwrite existing data with a Null value if the parameter value
> > > passed in is Null.
> > >
> > > Please advise if there's a simpler way of doing this.
> > >
> > > Thanks,
> > >
> > > Scott
> > >
> > >
> > >
> > > CREATE PROCEDURE [update_OrderFromDataImport]
> > >  (
> > >  @Order_GUID    uniqueidentifier,
> > >  @Order_ActualArrivalDate smalldatetime,
> > >  @Order_ActualShipDate  smalldatetime,
> > >  @Order_BOL   varchar(50)
> > >  )
> > > AS
> > > IF @Order_ActualArrivalDate <> Null
> > >    BEGIN
> > >    BEGIN TRANSACTION
> > >    UPDATE [psOrder]
> > >    SET [ActualArrivalDate] = @Order_ActualArrivalDate
> > >    WHERE [GUID]  = @Order_GUID
> > >    COMMIT TRANSACTION
> > >    END
> > >
> > > IF @Order_ActualShipDate <> Null
> > >    BEGIN
> > >    BEGIN TRANSACTION
> > >    UPDATE [psOrder]
> > >    SET [ActualShipDate] = @Order_ActualShipDate
> > >    WHERE [GUID]  = @Order_GUID
> > >    COMMIT TRANSACTION
> > >    END
> > >
> > > IF @Order_BOL <> Null
> > >    BEGIN
> > >    BEGIN TRANSACTION
> > >    UPDATE [psOrder]
> > >    SET [BOL] = @Order_BOL
> > >    WHERE [GUID]  = @Order_GUID
> > >    COMMIT TRANSACTION
> > >    END
> > >
> > >
> > >