stored procedure parameter question

[lfc77]

I have 9 different parameters which are coming in from an XML document, and they may or may not be entered by the user. When I am parsing the XML I am setting a string to the value of the relevant parameter, so if the value is entered by the user, the string will equal that value, otherwise the string will be empty.

I then want to pass these parameters to a stored procedure to update a table, but obviously if the string is empty I don't want this particular field to be updated as it would overwrite a value that may already be in there with an empty string.

I can do this in SQL in my code by building up a string and executing it, but I can't think of how I can do this with a stored procedure, given that I need to make the stored procedure have the ability to receive all of the 9 parameters, whether they are empty or not, and to write a different Update statement for every combination of these paramters would take too long.


Can anybody help me out with this?


Any help would be really appreciated.


Cheers,

lfc77

 

[JohnDTampaBay]

Is this what you're looking for? Good luck!

CREATE PROC uspUpdateTable
 , @RowID int
 , @Param2 char(10)
 , @Param3 char(10)
 , @Param4 char(10)
AS
UPDATE MyTable
SET Column2 = IsNull( NullIf( @Param2 , '' ) , Column2 )
  , Column3 = IsNull( NullIf( @Param3 , '' ) , Column3 )
  , Column4 = IsNull( NullIf( @Param4 , '' ) , Column4 )
WHERE RowID = @RowID

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[lfc77]

John,

That isn't quite what I was trying to do. If the Param is empty, I DON'T want to update the field....is this possible?


Cheers,

lfc77

 

[dbomrrsm]

Try this although its a bit of a stab in the dark.

CREATE PROC uspUpdateTable
@RowID int
, @Param2 char(10)
, @Param3 char(10)
, @Param4 char(10)
AS
UPDATE MyTable
SET Column2 =
CASE @Param2
WHEN Null THEN Column2
ELSE @Param2
END,
Column3 =
CASE @Param3
WHEN Null THEN Column3
ELSE Param3
END,
Column4 =
CASE Param4
WHEN Null THEN Column4
ELSE @Param4
END

[blue]DBomrrsm[/blue]

 

[dbomrrsm]

Slight tweak and this works

CREATE PROC uspUpdateTable
@RowID int
, @Param2 char(10)
, @Param3 char(10)
, @Param4 char(10)
AS
UPDATE MyTable
SET Column2 =
CASE @Param2
WHEN Null THEN Column2
ELSE @Param2
END,
Column3 =
CASE @Param3
WHEN Null THEN Column3
ELSE @Param3
END,
Column4 =
CASE @Param4
WHEN Null THEN Column4
ELSE @Param4
END
WHERE @RowID = IDNT


--EXEC uspUpdateTable 2,'three','cheers',null

[blue]DBomrrsm[/blue]

 

[lfc77]

This seems to work :

UPDATE MyTable
SET Column2 =
CASE @Param2
WHEN '' THEN Column2
ELSE @Param2
END


Cheers,

lfc77

 

[dbomrrsm]

Just a quick check and I guess you dont need to check as you say the above works - but :

Are you sure the calling app passes a null string through as '' ?

I only suggest checking because if you have your proc as

UPDATE MyTable
SET Column2 =
CASE @Param2
WHEN '' THEN Column2
ELSE @Param2
END

and null is passed through instead of '' then the field WILL get updated and will show NULL rather than the previous entry which I think is what you want.


[blue]DBomrrsm[/blue]

 

[lfc77]

DBomrrsm,

Yes, all the string variables in my app are initialising to "" before I try to populate them with the contents of an XML file. If the relevant tag in the XML file is there, then I populate the string with it, otherwise I leave the string in the state it was in when it was initialised.

Thanks for your help anyway...the stored procedure I am now using is basically the one you suggested but with that minor change.


Cheers,

lfc77

 

[dbomrrsm]

no problem - from your name can I deduce that you support Liverpool FC - the third best team on Merseyside ?



[blue]DBomrrsm[/blue]

 

[JohnDTampaBay]

This code will NOT update the column if the value passed to the parameter is empty string. The NULLIF function checks for the empty string, and substitutes NULL if found. So, the ISNULL function substitutes the current column value if the parameter is NULL or the parameter is empty string - effectively not updating the column at all.

CREATE PROC uspUpdateTable
 , @RowID int
 , @Param2 char(10)
 , @Param3 char(10)
 , @Param4 char(10)
AS
UPDATE MyTable
SET Column2 = IsNull( NullIf( @Param2 , '' ) , Column2 )
  , Column3 = IsNull( NullIf( @Param3 , '' ) , Column3 )
  , Column4 = IsNull( NullIf( @Param4 , '' ) , Column4 )
WHERE RowID = @RowID

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[dbomrrsm]

I was looking at your code earlier on today and it seemed to make sense although I only glanced at it - glad to see it works.

Wonder which of the solutions works better ?

Shows here is always more than one way to skin a cat.

[blue]DBomrrsm[/blue]