Hello all,

I'm designing an application and writing stored procedures for it.

I'd like to have one stored procedure for each table that handles the
UPDATES.  Depending on what parameters are passed in, those fields will
be updated.  Sometimes it might be all the fields, sometimes maybe just
one or two.

should I create a separate UPDATE stored procedure for each field?

I'll be calling these from methods in a class.  The methods will be
separated out by field.

For instance:

SetName, SetDescription, and SetAbbreviation will all be methods.

Any help or advice would be much appreciated!

Thanks,
Andy

I would just write one SP to update a table.  From a performance perspective
it makes more sense to call one SP with three parameters than 3 SP's with
one parameter each.  You can use COALESCE() to achieve the effect you're
looking for:

CREATE PROCEDURE dbo.UpdatePeople (@Identifier INT,
    @Name VARCHAR(30) = NULL,
    @Description VARCHAR(50) = NULL,
    @Abbreviation VARCHAR(5) = NULL)
AS
UPDATE People
SET Name = COALESCE(@Name, Name),
    Description = COALESCE(@Description, Description),
    Abbreviation = COALESCE(@Abbreviation, Abbreviation)
WHERE Identifier = @Identifier
GO

In this example, if you don't pass in @Description and @Abbreviation
parameters, it will change only the Name column (the other columns will be
updated with existing information).

<andym***@gmail.com> wrote in message
Show quoteHide quote

Fantastic, thanks for the advice!


Mike C# wrote:
Show quoteHide quote

#include <darth_vader.h>
printf("Impressive.  MOST impressive.");

That is the slickest technique.  I would never have thought of that.  This
now lets me rethink some of the way I build systems, because it lets me
pretend that I have overloading in stored procedures so I can call them from
my overloaded .NET Framework methods.

And since the doofus .NET DataAdapter internally updates one record at a
time, I don't have to worry about the performance cost of executing the
function once for each record, because that's the way the DataAdapter
behaves anyway.

Thanks!

Show quote Hide quote Thanks.  Now if I could just get nominated for MVP status...  I sure could
use a free MSDN subscription.  ha

Show quote Hide quote Just as an FYI, I use this technique a lot, but a problem I've run up
against once or twice in "the real world" is that there is no way to
distinguish between [explicitly passing NULL, meaning "erase the existing
value"] and [leaving the parameter out, meaning "leave the value that
already exists"].  I had at least one case where the requirement from the
application side was to allow the parameter to be optional, but NULL really
means NULL.  So, it got a little more complex, where I set the default
values to some ridiculous token value and had to do a CASE to figure out
what it really meant.

In most applications, I think this is perfectly fine, just warning those
that might have slightly different requirements than the OP.

A

If I read you correctly, are you describing a case where there is an
existing non-null value, and the application is explicitly trying to replace
that with a NULL.

Then COALESCE() gets confused and doesn't update the value in the database.

Correct?

Yes, pretty close.  COALESCE doesn't get confused really; you've told it to
take the first non-null parameter, and when I explicitly pass NULL in,
that's not it.

True, I sometimes use -1 for INTs or some edge-case token for other data
type parameters if I want them to be able to accept a NULL value.  Those are
fairly easy to check for with CASE as you pointed out, or even with COALESCE
and NULLIF if you want to save a few keystrokes:

CREATE PROCEDURE dbo.UpdatePeople (@Identifier INT,
    @Name VARCHAR(30) = NULL,
    @Description VARCHAR(50) = NULL,
    @Abbreviation VARCHAR(5) = NULL,
    @Age INT = NULL) -- Age Column in the table will allow NULLs, and no one
     -- can be less than 0 years old, so we'll use -1 as the indicator that
it should
     -- not be changed instead of using NULL
AS
UPDATE People
SET Name = COALESCE(@Name, Name),
    Description = COALESCE(@Description, Description),
    Abbreviation = COALESCE(@Abbreviation, Abbreviation),
    Age = COALESCE(NULLIF(@Age, -1), Age)
WHERE Identifier = @Identifier
GO

For a variety of options, refer to:
http://www.sommarskog.se/dyn-search.html

Dyncamic SQL
ORDER BY clause with unknown column name
Second try: Need a little help with a sql query
how to select
Dynamic query in a function