April 25, 2010 at 11:00 am
I have a database that has a few dozen tables. Those tables, in total, contain about a 175 columns. I need to write stored procedures to update these columns and was wondering how do I go about doing that for most of those columns? For example, I have a contact table. If I need to update the contact's address, on one occasion, but nothing else do I write a SP just to update that column? If on another occasion I just need to update the phone number, do I need to have yet another SP to do that? Then in the event of multiple field updates...? Or, is it better just to have one SP to update the entire record each time?
Thanks for your time and help
April 25, 2010 at 11:25 am
Generally, the approach that is taken is to update the row and specify all of the column values. In your application, you would get all of the data - and when the user saves the data you would update all of the columns.
However, it really depends upon the functionality you are creating. If your application only allows certain columns to be updated, then you would create a procedure (or code) that just updates those columns.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 25, 2010 at 10:24 pm
I would use Dynamic Update query in this context... But we must also write that dynamic query to neglect SQL-Injections... But when the number of columns to be updated is not sure, then i would cetainly go for Dynamic SQL..
April 25, 2010 at 11:13 pm
Whichever method you choose, make sure you do your updates in one transaction. For example, if you decide to use multiple stored procs to update a record, StoredProc_1 executes successfully, but then StoredProc_2 fails, you should rollback StoredProc_1 if they were both supposed to update one record. You don't want to end up with partially saved records.
Try to see if Dynamic SQL suits your needs. It's probably your best bet.
April 26, 2010 at 7:29 am
From the sounds of it, the tables aren't terribly well normalized. This means you're going to be moving a lot more data around every time you want to do updates than you should.
That said, most updates have a where clause, be sure that the columns used there are well indexed so that you efficiently find the rows you need to update.
As someone else said, make sure you do as much work as possible within a given transaction. It's better to update two columns in one call rather than two calls. It's better to update two rows in one call rather than two calls. That all depends on the app, what's getting updated, by who, etc., of course. It's just a general rule.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 26, 2010 at 9:28 am
Thanks EVERYBODY for the great help. Back in the late 80's I was sent through, I think, Sybase's entire line of courses. You'd think I'd remember the updates nearly 20 years later. I haven't worked with databases much since. I do remember about rolling things up into transactions and all, but thanks for that important reminder. I inherited a project with databases only because I am about the only one who can spell SQL in my company, much less pronounce it... It isn't normalized much at all and I've been told that "we" don't have the luxury of time and/or funds to redesign it--people will never learn... I've been asked to port a windows app to a web app and the normal "need it by yesterday" time to finish it. Know I need to read up on dynamic SQL... Thanks again all. Take care.
April 26, 2010 at 11:56 am
George i already have coded a piece of dynamic update stored procedure.. now that am at home, i will give u the code tomorrow, so spare another 12 hrs for me, you will get a start-up version of the code...
April 26, 2010 at 7:43 pm
Very considerate and helpful CoolCoffee. Thank you very much!
April 26, 2010 at 11:50 pm
Hey Gerald, as promised , here is mini-version of the dynamic update query.. It is just a starter, you will have to build up on the start and customize it for your needs..
Note: This is just full update query. The capability to identify the old value and update it with new value (you can do it easily using WHERE clause in the dynamic query) is upto you to handle.
SET NOCOUNT ON
IF OBJECT_ID('TEMPDB..#TEMP') IS NOT NULL
DROP TABLE #TEMP ;
CREATE TABLE #TEMP
(
NAME VARCHAR(2),
CITY VARCHAR(10),
AGE INT,
GENDER VARCHAR(1),
SALARY INT
);
INSERT INTO #TEMP
SELECT 'A' , 'CHENNAI' , 20 , 'M' , 100 UNION ALL
SELECT 'A' , 'CHENNAI' , 25 , 'M' , 100 UNION ALL
SELECT 'A' , 'CHENNAI' , 20 , 'F' , 80 UNION ALL
SELECT 'B' , 'MUMBAI' , 20 , 'M' , 100 UNION ALL
SELECT 'B' , 'KOLKATA' , 25 , 'M' , 100 UNION ALL
SELECT 'B' , 'DELHI' , 20 , 'F' , 80
SELECT * FROM #TEMP ;
IF OBJECT_ID('dbo.UPDATE_SALARY_DETAILS') IS NOT NULL
AND OBJECTPROPERTY(OBJECT_ID('dbo.UPDATE_SALARY_DETAILS'), 'IsProcedure') = 1
BEGIN
PRINT 'Dropping Stored procedure UPDATE_SALARY_DETAILS...'
DROP PROCEDURE dbo.UPDATE_SALARY_DETAILS
END
GO
CREATE PROCEDURE dbo.UPDATE_SALARY_DETAILS
@NAME VARCHAR(2) = '',
@CITY VARCHAR(10) = '',
@AGE INT = 20,
@GENDER VARCHAR(1) = '',
@SALARY INT = 0
AS
BEGIN
DECLARE @QUERY VARCHAR(4000)
DECLARE @UPDATEPART VARCHAR(2000)
--== INITIAL UPDATE QUERY
SELECT @QUERY = '' , @UPDATEPART = ''
SET @QUERY = 'UPDATE #TEMP SET '
--== FROM HERE ON, WE WILL BUILD THE UPDATE STATEMENT FOR EACH COLUMN
IF ISNULL(@NAME,'') <> ''
BEGIN
SET @UPDATEPART = @UPDATEPART + 'NAME = ''' + @NAME + ''','
END
IF ISNULL(@CITY,'') <> ''
BEGIN
SET @UPDATEPART = @UPDATEPART + 'CITY = ''' + @CITY + ''','
END
IF ISNULL(@AGE,0) <> 20
BEGIN
SET @UPDATEPART = @UPDATEPART + 'AGE = ' + CAST(@AGE AS VARCHAR(100))+ ','
END
IF ISNULL(@GENDER,'') <> ''
BEGIN
SET @UPDATEPART = @UPDATEPART + 'GENDER = ''' + @GENDER + ''','
END
IF ISNULL(@SALARY,0) <> 0
BEGIN
SET @UPDATEPART = @UPDATEPART + 'SALARY = ' + CAST(@SALARY AS VARCHAR(100))+ ','
END
SET @QUERY = @QUERY + @UPDATEPART
--==REMOVE TRAILING "," FROM THE FINAL QUERY
SET @QUERY = LEFT(@QUERY,DATALENGTH(@QUERY)-1)
--== CHECKING IF ATLEAST ONE COLUMN IS GETTING UPDATED; IF NOT, EXIT.
IF CHARINDEX('=',@QUERY,0) < 1
BEGIN
PRINT 'NOTHING TO UPDATE'
END
ELSE
BEGIN
PRINT @QUERY
--EXEX (@QUERY)
END
END
GO
--EXEC UPDATE_SALARY_DETAILS @GENDER = 'F' , @SALARY = 1000 , @AGE = 12
Hope this helps you!
Cheers!!
April 28, 2010 at 10:06 am
Although I don't fully understand how the union works, your code really made my understanding dynamic SQL very clear. I had envisioned something messier. ColdCoffee I really appreciate your time and help. I'm sure this will help others, as well.
George
April 28, 2010 at 8:36 pm
My pleasure, explaining and learning with you, George!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply