|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Question about best practices..
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,LastAccessed), 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. Show quoteHide quote "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,LastAccessed), > 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 .... 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. Show quoteHide quote "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message 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 We enforce that they get all the details from a generic _GetDetails > 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. 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
Show quote
Hide quote
On 17 Apr, 21:51, "Tim Greenwood" <tim_greenwood AT yahoo DOT com> If you regularly need to update some small subset of columns then itwrote: > 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,LastAccessed), > 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! 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 -- 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! Show quoteHide quote "TheSQLGuru" <kgbo***@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. > > >  
Other interesting topics
Rolling Back from build 3159 to build 3152
SQL 2005 Performance One table returns ODBC timeout error can't access server from another PC Storing T-SQL code so it can't be read Change only USER tables to a new user Major SQL Server 2k5 x64 configuration problem ssrs Multiple database versions on same server. SQL 2000 SP4 Backup Fails but no error? |
|||||||||||||||||||||||
