Tom Cooper
3/15/2007 3:48:00 PM
Replace
iPurpose = @iPurpose,
imgPlatePDF = @pPDF, (I want to exlude this if parm is null)
dtSteelPlateMod = getdate()
with
iPurpose = @iPurpose,
imgPlatePDF = COALESCE (@pPDF, imgPlatePDF),
dtSteelPlateMod = getdate()
then if @pPDF is not null, imgPlatePDF will be updated to @pPDF, and if
@pPDF is null, imgPlatePDF will be updated to itself and the value will not
change.
Tom
"Brad Pears" <bradp@truenorthloghomes.com> wrote in message
news:usxMDWxZHHA.208@TK2MSFTNGP05.phx.gbl...
>I have a stored procedure that either inserts a row or updates a row.
>There is a field in there that I would like to check for in the update
>section. If the incoming parameter (pPDF) is null, I do not want to even
>issue an update for that field. Can I cehck for this inline or do I have to
>write another complete update statement again which would simply exclude
>that field if the incoming parm is null?
>
> Here is the stored procedure...
>
> CREATE PROCEDURE dbo.sp_UpdateSteelPlateItem
> (
> @cSku as char(30),
> @vcWidth1 as varchar(50),
> @vcWidth2 as varchar(50),
> @vcWidth3 as varchar(50),
> @vcWidth4 as varchar(50),
> @iSkewed as integer,
> @vcThickness as varchar(50),
> @iHoles as integer,
> @iType as integer,
> @iApplication as integer,
> @iPurpose as integer,
> @pPDF as image = null (this is the parm I want to check for null)
> )
>
> AS
> --either update or insert into SteelPlates
> if exists (select cSKU from tblSteelPlates where cSKU=@cSku)
> begin
> update tblSteelPlates set
> cSku = @cSKU,
> vcWidth1 = @vcWidth1,
> vcWidth2 = @vcWidth2,
> vcWidth3 = @vcWidth3,
> vcWidth4 = @vcWidth4,
> iSkewed = @iSkewed,
> vcThickness = @vcThickness,
> iHoles = @iHoles,
> iType = @iType,
> iApplication = @iApplication,
> iPurpose = @iPurpose,
> imgPlatePDF = @pPDF, (I want to exlude this if parm is null)
> dtSteelPlateMod = getdate()
> where cSku = @cSku
> end
> else
> begin
> INSERT INTO tblSteelPlates (cSku,vcWidth1, vcWidth2, vcWidth3,
> vcWidth4,iSkewed, vcThickness,iHoles, iType, iApplication, iPurpose,
> imgPlatePDF, dtSteelPlateInsert)
> VALUES (@cSku, @vcWidth1, @vcWidth2, @vcWidth3, @vcWidth4,
> @iSkewed, @vcThickness, @iHoles, @iType, @iApplication, @iPurpose, @pPDF,
> getdate())
> end
> GO
>
>
>
>
> Thanks, Brad
>
>