Question about best practices..

Given the need to update data, is it better to have 500 procs that update a
table as needed for a specific situation or given a table like:
create table dbo.People
(
ID int identity(1,1) not null,
LastAccessed datetime null,
FirstName varchar(20) not null,
LastName varchar(30) not null,
Address1 varchar(50) not null,
Address2 varchar(50) not null,
City varchar(50) not null,
State char(2) not null
)
to have a proc like this that can be used for any update to this table:
create procedure UpdatePeople
@.ID int,
@.LastAccessed datetime=null,
@.FirstName varchar(20)=null,
@.LastName varchar(30)=null,
@.Address1 varchar(50)=null,
@.Address2 varchar(50)=nul,
@.City varchar(50)=null,
@.State char(2)=null
as
set nocount on;
update dbo.People
set LastAccessed=isnull(@.LastAccessed,LastAc
cessed),
FirstName=isnull(@.FirstName, FirstName),
LastName=isnull(@.LastName, LastName),
Address1=isnull(@.Address1, Address1),
Address2=isnull(@.Address2, Address2),
City=isnull(@.City, City),
State=isnull(@.State,State)
where ID=@.ID
return @.@.error
GO
Thanks !! Inquiring minds want to know (mine!) and I'm tired of seeing
developers throwing hundreds of procs at me when it seems unecessary!Personally, I would prefer to have a CRUD interface that takes all the
values out, and then updates all of the values when saving.
The problem with trying to preserve a few bytes like you are doing, is that
now you can't overwrite an existing value with NULL. Yes, you can pass
Address1 = '' but is that really the same thing as NULL? I don't think so,
but it really depends on overall requirements.
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:%23NHluITgHHA.5052@.TK2MSFTNGP05.phx.gbl...
> Given the need to update data, is it better to have 500 procs that update
> a table as needed for a specific situation or given a table like:
> create table dbo.People
> (
> ID int identity(1,1) not null,
> LastAccessed datetime null,
> FirstName varchar(20) not null,
> LastName varchar(30) not null,
> Address1 varchar(50) not null,
> Address2 varchar(50) not null,
> City varchar(50) not null,
> State char(2) not null
> )
>
> to have a proc like this that can be used for any update to this table:
> create procedure UpdatePeople
> @.ID int,
> @.LastAccessed datetime=null,
> @.FirstName varchar(20)=null,
> @.LastName varchar(30)=null,
> @.Address1 varchar(50)=null,
> @.Address2 varchar(50)=nul,
> @.City varchar(50)=null,
> @.State char(2)=null
> as
> set nocount on;
> update dbo.People
> set LastAccessed=isnull(@.LastAccessed,LastAc
cessed),
> FirstName=isnull(@.FirstName, FirstName),
> LastName=isnull(@.LastName, LastName),
> Address1=isnull(@.Address1, Address1),
> Address2=isnull(@.Address2, Address2),
> City=isnull(@.City, City),
> State=isnull(@.State,State)
> where ID=@.ID
> return @.@.error
> GO
>
> Thanks !! Inquiring minds want to know (mine!) and I'm tired of seeing
> developers throwing hundreds of procs at me when it seems unecessary!
>
>|||Also, typically if you are updating a person's info, you're not updating a
single value. e.g. if someone moves, you need to update address1, address2,
city, state, zip, etc.
Do you really want to manage this set of procedures, and call them all
individually? I wouldn't:
dbo.Person_UpdateAddress1
dbo.Person_UpdateAddress2
dbo.Person_UpdateCity
dbo.Person_UpdateState
dbo.Person_UpdateZip
dbo.Person_UpdatePhone
dbo.Person_UpdateFax
...
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006|||On 17 Apr, 21:51, "Tim Greenwood" <tim_greenwood AT yahoo DOT com>
wrote:
> Given the need to update data, is it better to have 500 procs that update
a
> table as needed for a specific situation or given a table like:
> create table dbo.People
> (
> ID int identity(1,1) not null,
> LastAccessed datetime null,
> FirstName varchar(20) not null,
> LastName varchar(30) not null,
> Address1 varchar(50) not null,
> Address2 varchar(50) not null,
> City varchar(50) not null,
> State char(2) not null
> )
> to have a proc like this that can be used for any update to this table:
> create procedure UpdatePeople
> @.ID int,
> @.LastAccessed datetime=null,
> @.FirstName varchar(20)=null,
> @.LastName varchar(30)=null,
> @.Address1 varchar(50)=null,
> @.Address2 varchar(50)=nul,
> @.City varchar(50)=null,
> @.State char(2)=null
> as
> set nocount on;
> update dbo.People
> set LastAccessed=isnull(@.LastAccessed,LastAc
cessed),
> FirstName=isnull(@.FirstName, FirstName),
> LastName=isnull(@.LastName, LastName),
> Address1=isnull(@.Address1, Address1),
> Address2=isnull(@.Address2, Address2),
> City=isnull(@.City, City),
> State=isnull(@.State,State)
> where ID=@.ID
> return @.@.error
> GO
> Thanks !! Inquiring minds want to know (mine!) and I'm tired of seeing
> developers throwing hundreds of procs at me when it seems unecessary!
If you regularly need to update some small subset of columns then it
may be worth creating a separate proc for such a case. On grounds of
maintainability I would question the value of creating 500 such procs
unless it's essential to squeeze every last ounce of performance from
the database.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||That's exactly my point...no I wouldn't want to but that is exactly what I
keep getting from developers. I'd prefer CRUD interface as well but I'm
lacking in support right now. Unfortunately all developers have to pass
their code through me for verification as dba before it goes into final QA
environment.
So other than the idea of supporting only modified values the basic idea
here seems right on? IOW get rid of the whole isnull() on the updates?
The only problem we run into with that is if they've used a custom view or
proc that only queries a subset of data from multiple tables then they don't
have all the values to supply to a CRUD proc for updating...I know these
are extremely basic issues here, I'm just playing devils advocate. I have
some authority to leverage in the enforcement of these things but want to be
sure I'm coming from a best (or at least accepted) practices.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:%23RO3DPTgHHA.1220@.TK2MSFTNGP03.phx.gbl...
> Also, typically if you are updating a person's info, you're not updating a
> single value. e.g. if someone moves, you need to update address1,
> address2, city, state, zip, etc.
> Do you really want to manage this set of procedures, and call them all
> individually? I wouldn't:
> dbo.Person_UpdateAddress1
> dbo.Person_UpdateAddress2
> dbo.Person_UpdateCity
> dbo.Person_UpdateState
> dbo.Person_UpdateZip
> dbo.Person_UpdatePhone
> dbo.Person_UpdateFax
> ...
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>|||> So other than the idea of supporting only modified values the basic idea
> here seems right on? IOW get rid of the whole isnull() on the updates?
> The only problem we run into with that is if they've used a custom view or
> proc that only queries a subset of data from multiple tables then they
> don't have all the values to supply to a CRUD proc for updating...I know
> these are extremely basic issues here, I'm just playing devils advocate.
> I have some authority to leverage in the enforcement of these things but
> want to be sure I'm coming from a best (or at least accepted) practices.
We enforce that they get all the details from a generic _GetDetails
procedure if their intention is to update even only one of the values.
If they are just getting the data for display, then yes I could see why they
might argue that they only want a subset of the data.
But with the memory on servers these days, there is no reason why the app
can't store the whole row in memory. Or, in cases like a two-column report,
that segment of the code can just ignore the other columns.
The trade-off here is performance vs. maintenance. You need to make that
decision... we can't tell you what's best because we don't know what it will
take to convince your developers to do it your way, and we don't know what
the performance threshold is (e.g. when does pulling/updating a subset
really change the way the app behaves).
A|||I have developed a WhichFieldsUsed mechanism that enables a single sproc to
only update the field or fields in a table that the caller wishes to have
updated, and then only if they are different from the existing values. Drop
me an email if you are interested. kgboles a t earth link d o t net.
TheSQLGuru
President
Indicium Resources, Inc.|||I already know *how* to do that. My question wasn't so much how to do it as
what is the most accepted practice.
Thanks!
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:%23eMdKFfgHHA.5044@.TK2MSFTNGP05.phx.gbl...
>I have developed a WhichFieldsUsed mechanism that enables a single sproc to
>only update the field or fields in a table that the caller wishes to have
>updated, and then only if they are different from the existing values.
>Drop me an email if you are interested. kgboles a t earth link d o t net.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
>
>