SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Updating Records
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

t1g312
Posting Yak Master

United Arab Emirates
148 Posts

Posted - 01/13/2004 :  23:59:48  Show Profile  Reply with Quote
Hi all,

Suppose I have a record with 50 fields. If I modify a single field from my front-end, how would I update the record using a SP? Do I return all the fields to the SP or only the one that has changed and construct an UPDATE query based on that?

One more question. If I use a delete query followed by an insert query instead of an update query, how much performance loss are we talking about?

Thanks!

Adi

-------------------------
/me sux @sql server

Nazim
A custom title

United Arab Emirates
1408 Posts

Posted - 01/14/2004 :  01:25:55  Show Profile  Reply with Quote
Hi there,

About ur first Q.
you dont have to send all the field values , remember the data is persistent in database until you changed it . for that you have to give a call a update statement and commit it.
Specify what data you are going to change. supposing you are just change a persons name, and all the other fields are same.
your Sp will be something like this


create procedure UpdateEmp_sp @mempno int, @mname varchar(30)
as
set nocount on
update employee set name=@mname where empno=@mempno
set nocount off

if you look at this stored procedure, you will notice , am just changing the name of the employee and not no other information thats the reason i am only passing in the sp the name to change and employeeno to identify which employee number to change.

depending upon what you are planning to change you have to give the list of parameters and write a update statement accordingly.


About your second question


An Update will be much faster then having a delete and insert.



-------------------------
What lies behind you and what lies ahead of you are small matters compared to what lies within you.-Ralph Waldo Emerson

Edited by - Nazim on 01/14/2004 01:30:04
Go to Top of Page

t1g312
Posting Yak Master

United Arab Emirates
148 Posts

Posted - 06/09/2004 :  06:59:14  Show Profile  Reply with Quote
Do you mean to say that if I have 50 fields in my record, I need to have that many stored procedures?

Adi

-------------------------
/me sux @sql server
Go to Top of Page

t1g312
Posting Yak Master

United Arab Emirates
148 Posts

Posted - 06/09/2004 :  07:11:39  Show Profile  Reply with Quote
As for my 2nd question, Delete followed by Insert seems to be better in that you don't need to have an "Update" button in your program. Just a "save" will do.

Adi

-------------------------
/me sux @sql server
Go to Top of Page

sghosh30
Starting Member

5 Posts

Posted - 06/09/2004 :  09:31:15  Show Profile  Reply with Quote
No way, Update consumes much less resources when compared to a delete + insert. For an update the indexes do not need to be recreated which might take some time for large data sets.
Go to Top of Page

t1g312
Posting Yak Master

United Arab Emirates
148 Posts

Posted - 06/09/2004 :  09:34:46  Show Profile  Reply with Quote
Hi,

Thanks for that. Do I need to pass back all the fields to the database even if just one was changed through the front-end?

Adi

-------------------------
/me sux @sql server
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 06/09/2004 :  12:36:51  Show Profile  Reply with Quote
We have SProcs which we refer to as "UpSerts" which are designed to save data from web pages.

These any allow any/all columns from the table to be passed to the SProc with the assumptions that:

Web input forms yield "" blank strings for 'empty' data
It is not possible to generate a NULL value from a web form

So, all parameters to the Sproc default to NULL. If their value is NULL it can only be because it was not provided to the Sproc and it is set to NULL by default; when a column parameter is NULL the Sproc will PRESERVE any existing data in that column.

Also, all parameters are in VARCHAR format, so non-varchar columns are converted to appropriate datatypes (using local variables)

The reason for this is that we NEED to be able to change, say, a date from "Present" to "Empty" - someone "removes" the value from a date field, so now we have to store a NULL.

Thus parameters containing EMTPY strings (rather than NULL which indicates 'no value provided') are indications to store NULL in the database.

We took the decision to NOT store empty strings in the database - i.e. all empty strings become NULLs in the DB - this may not suit everyone!


CREATE PROCEDURE my_SP_MyTable_Save
   @MyPKColumn1 varchar(nn),
   @MyPKColumn2 varchar(nn),    -- This is actually type INT
   @MyColumn3 varchar(nn)=NULL,
   @MyColumn4 varchar(nn)=NULL  -- This is actually type DATETIME
AS
DECLARE @int_MyPKColumn2 int,
        @dt_MyColumn4 datetime

-- Convert non-varchar columns to appropriate type
SELECT @int_MyPKColumn2 
           = CASE WHEN @MyPKColumn2 = '' THEN NULL 
                  ELSE CONVERT(int, @MyPKColumn2) END,
       @dt_MyColumn4 
           = CASE WHEN @MyColumn4= '' THEN NULL 
                  ELSE CONVERT(datetime, @MyColumn4) END

IF EXISTS 
(
    SELECT * 
    FROM MyTable 
    WHERE     MyPKCOlumn1 = @MyPKCOlumn1 
          AND MyPKCOlumn2 = @int_MyPKColumn2
)
BEGIN
    -- Update required
    UPDATE MyTable
    SET    MyColumn3 = CASE WHEN @MyColumn3 = '' 
                       THEN NULL 
                       ELSE COALESCE(@MyColumn3, MyColumn3) END,
-- NOTE The varchar column is used here, NOT the @dt_XXX column
           MyColumn4 = CASE WHEN @MyColumn4 = ''  
                       THEN NULL 
-- NOTE The @dt_XXX column is used here!
                       ELSE COALESCE(@dt_MyColumn4, MyColumn4)
    WHERE      MyPKColumn1 = @MyPKColumn1 
           AND MyPKColumn2 = @int_MyPKColumn2
END
ELSE
BEGIN
    -- New record, INSERT
    INSERT INTO MyTable 
    (
       MyPKColumn1,
       MyPKColumn2,
       MyColumn3,
       MyColumn4
    )
SELECT @MyPKColumn1,
       @int_MyPKColumn2,
       CASE WHEN @MyColumn3 = '' THEN NULL ELSE @MyColumn3 END,
       CASE WHEN @MyColumn4 = '' THEN NULL ELSE @dt_MyColumn4 END
END


Kristen

Edited by - Kristen on 06/10/2004 01:25:55
Go to Top of Page

byrmol
Shed Building SQL Farmer

Australia
1591 Posts

Posted - 06/10/2004 :  01:36:58  Show Profile  Reply with Quote
1st Question: You can write an injection safe dynamic sql procedure to handle any update combination. Check this post http://weblogs.sqlteam.com/davidm/archive/2004/04/07/1210.aspx for a solution base.

2nd Question: Relationally an update is a delete followed by an insert. SQL Server can choose to do an update in place or a delete then an insert....

DavidM

"Always pre-heat the oven"
Go to Top of Page

t1g312
Posting Yak Master

United Arab Emirates
148 Posts

Posted - 06/10/2004 :  02:35:49  Show Profile  Reply with Quote
Thanks guys!

Adi

-------------------------
/me sux @sql server
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.1 seconds. Powered By: Snitz Forums 2000