Update data by using Stored Procedure

Hello Experts
We have a database application project (.net ,Ado.Net  , Entity Framework, Visual Studio Windows Form ) that complteley use Stored procedures  so the developers  call the stored and pass the required parameters to it ...
Am working on creating these Stored Proceders.... every thing is good for Delete , Select , Insert Processess But the problem in Update Procedure for the follwoing reason:-
suppose i have a table with 30 Columns
so i create the following Sp
Create Proc UpdateMyTable
(
@ID int
,@Col1 int
,Col2 Nvarcahr(250)
,Col3 Date
,.........
.
.
.
, Col30 nvarchar(30)
)
AS
UPDATE  mytable SET Col1=@Col1 ,Col2=@Col2 ,Col3=@Col3 ,...........Col30=@Col30
Where ID=@ID
--------
The problem is in all cases it supposed that the user will change/Update ONLY Two or Three column in specific row
So the stored will take All parameters even for the UNCHANGED column values... then update the table
in toher words the Client App will pass the all values to stored procedures even the user Just change the column 3 value
I dont know how to handle this in stored Procedure to only update the changed values to be more efficient for performance.
ali_alannahAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jawaharroyCommented:
Pass the colid, colvalues parameters from the front end.

For example created procedure as below

CREATE PROC updatetable
(
@Coliid varchar(100),
@colvaues varchar(100)
)
AS
BEGIN
---Update staement here. you can use dyanamic SQL string. execute the sql sring the sp_executesql
END

Colid will be comma seprated values that can be fetched from information_schema.columns. Fetch the ordinal position for the table

SELECT ORDINAL_POSITION FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'test'

For example if you want to update the 3, 6 column values. Pass this values into colid 3,6.

Pass the values of the colid 3,6 in colvalues variable 10,'test'

In stored proecedure find the column name of the colid 3,6 from information_schema.columns

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'test' AND ORDINAL_POSITION = 3

Construct sql update statement using looping statements or XML(constuct a set class of an update statement.

by this way you can use for any tables(by passing table name as input), also no need create variables as equal to the no of columns in table

0
ali_alannahAuthor Commented:
Hello
My Main goal is to reduce the performance impact by passing ONLY changed values  
So with the solution above no gurantee for improving the performance because it do more steps
0
jawaharroyCommented:
Hi Ali,

By this way also you only need to changed values( The values you want to update) in COLID, COLVALUES varribale.

The sample foot print given in your inital post, decalred each variable for the no of columns present in the table. I am sure my method defitenley improve performance. Might be I am not understand your requirement. Let me know if you findout any other methods
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
you won't change any performance by changing only the columns that actually changed, because in the transaction log, all column values (old and new) will be logged, if they changed or not (exception for the out-of-row columns which are of TEXT, IMAGE and VARCHAR(MAX) columns that are so large that they are not stored "in row block".

so, your attempts to improve performance won't improve any performance, actually.

even with indexes on the fields it won't change, because if the value does not change, the index is not touched.
0
ali_alannahAuthor Commented:
Hi angelIII
what i meant that instead of send 600 Byte I only send 20 Byte so the going data via the netwrok will be less so the performance will be enhanced ... this my thought ..is it right?

So the method  what I'm using is OK?
I means is this way is standard to update data by using SP passing all values ?
Thanks angelIII
0
jogosCommented:
If you can call only a procedure passing 2 parameters to update those 2 parameters it can be faster especialy when having a large data. But for that you can also split a table so you don't have to pass the image of a product any time you want to just update the price or stock of that product.

With not null columns passed as null you can detect if in your procedure you  can choose a specific update-statement. But combinations will be uncountable so efficency and maintainebility? -> limited choise (again for example image is passed as NULL you use an update of all columns but the image)  

But colid, colvalues-solution involves or dynamic sql or multiple updates when different columns change so they both have it's own performance downside.

0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what i meant that instead of send 600 Byte I only send 20 Byte so the going data via the netwrok will be less so the performance will be enhanced ... this my thought ..is it right?

I see your point, and do agree 200% with it.
well, for that, you can do it by using 2 parameters for each column, like:

Create Proc UpdateMyTable
(
@ID int
,@Col1 int, @set_col1 tinyint
,Col2 Nvarcahr(250), @set_col2 tinyint
,Col3 Date, @set_col3 tinyint
,.........
)
AS
UPDATE  mytable 
  SET Col1=case when @set_col1 = 1 then @Col1 else col1 end
    ,Col2= case when @set_col2 = 1 then @Col2 else col2 end
   ,Col3= case when @set_col3 = 1 then @Col3 else col3 end
    ,...........
Where ID=@ID
-------- 

Open in new window


and you call it with each "@set_colX" with 1 for those you want to actually update, and null/0/whatever else to say "don't update that column" ...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CodeCruiserCommented:
One option is to use dynamic sql by passing the key=value pairs comma separated but you would need to do more work on client side to construct this string.

For example, your stored procedure only takes the ID and SQLStr parameters and the SQLStr parameter contains

col1=val1, col2=val2

or

col2=val2, col5=val5, col6=val6

etc

then you construct the full sql statement and execute it within stored procedure.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.