newbie - need to make an update sp, do i need all columns in it?

Question

Post reply

newbie - need to make an update sp, do i need all columns in it?

azarcomp

SSC Rookie

Points: 36
More actions
July 10, 2010 at 10:33 pm

#94984

SO I finally got hired and am rusty as can be. so much its embarrassing.
I need to make a sp that the vb.net forms controls will pass and updated values too.
so do i make one update sp for the form to call and pass all values too?
like I went to the view i created and went to scripts to clipboard as Update and it gives me a skeleton for every column. of course i know the PK will not be updated but the rest of the columns?
so if i did like this, what would i do if only 1 column needed to be updated and not all 50 of them.
i guess maybe the set line could have a parm?
set @columnToBeUpdated = @Value,
but then what if there are 6 or 8 to be updated?
As u can see, I desparately need to be pointed in the right direction.
any help greatly appreciated

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply

rvasanth SSC Eights! Points: 873 More actions · Answer 1

Yes, You have to mention all the columns in the SP which are all needs to update

update table set col1=@newvalue1, col2= @newvlaue2, etc...

But there are options for passing values to the SP

1. You can pass it as seperate parameter for each value

2. You can convert your values as an xml (put all the values in DataTable and DataTable.WriteXml(..) will give you in xml format) and pass as a single parameter to the SP and using OPENXML you can update

Declare @hdoc int

exec sp_xml_preparedocument @hdoc output, @xmldoc

Update Table set col1=newvalue1, col2=newvalue2...

from openxml(@hdoc, 2)

with (newvalue1 as varchar, new value2 as varchar....)

azarcomp SSC Rookie Points: 36 More actions · Answer 2

thank you very much ! that points me in the right direction. I wont do the xml right now becuase i already know the lead developer does not use it, and i need to make it work as simply as possible.:-D:-D:-D:-D

azarcomp SSC Rookie Points: 36 More actions · Answer 3

So what will I do when there is no value to be passed to un changed parms. they can't pass in anything or would ruin the row.

so if only @col2 needed to updated, what to pass to the rest?

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

azarcomp (7/11/2010)
so if only @col2 needed to updated, what to pass to the rest?

Here is a refresher article on that topic

http://msdn.microsoft.com/en-us/library/aa260662(SQL.80).aspx

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

azarcomp SSC Rookie Points: 36 More actions · Answer 5

Thanks, I looked at the linked, bit it looked to complicated for me right now. I am very rusty and just trying to build my knowledge up 1 step at a time. They need me to get this done very soon or will not be happy:w00t:

Ok, well I am trying to narrow it down before I start coding it for the development app, this is not the dev tables or data.just trying to see if someone thinks i am on the right track.

now I am trying to figure out if the only and best wat to update into 2 or more tables at the same time is to use 2 or more update queries.

like so:

1.make the updates for each table

update Customersset Name = 'TwinBob'where customerId = 1;update dbo.addressset Street = 'newstreet Rd'where CustomerID = 1

--Below select just to view my update changes--select c.CustomerID,c.Name,a.AddressID,a.Street from Customers c left join address aon c.CustomerID = a.CustomerID

am I on the right track???

2. Then make parameters for each column of each table and make the whole batch a stored procedure (T-sql).

3. Then in VB.net pass the vales of columns to be updated to the stored proc from vb.net.

Thanks VERY much in advance

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 6

azarcomp (7/11/2010)
Thanks, I looked at the linked, bit it looked to complicated for me right now. I am very rusty and just trying to build my knowledge up 1 step at a time. They need me to get this done very soon or will not be happy:w00t:
Ok, well I am trying to narrow it down before I start coding it for the development app, this is not the dev tables or data.just trying to see if someone thinks i am on the right track.
now I am trying to figure out if the only and best wat to update into 2 or more tables at the same time is to use 2 or more update queries.
like so:
1.make the updates for each table
update Customersset Name = 'TwinBob'where customerId = 1;update dbo.addressset Street = 'newstreet Rd'where CustomerID = 1
--Below select just to view my update changes--select c.CustomerID,c.Name,a.AddressID,a.Street from Customers c left join address aon c.CustomerID = a.CustomerID
am I on the right track???
2. Then make parameters for each column of each table and make the whole batch a stored procedure (T-sql).
3. Then in VB.net pass the vales of columns to be updated to the stored proc from vb.net.
Thanks VERY much in advance

What you're trying to do is reasonably simple. If you think that the linked pointed to something that was too complicated, then to me that's a good sign that you are not qualified to do what you've been tasked to do. I don't think you're rusty. I think you're inexperienced.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 7

Here's a skeleton, though as Alvin mentioned, if you don't understand Books Online, you are in for a rough time. Please get someone to help you go over the structure of things.

Create proc myproc

@pk int

, @param1 int = null

as

if @param1 is not null

update myTable

set col1 = @param1

where pkCol = @pk

return

You'd have to repeat the params, IF, UPDATE for all columns.

Or you can do one statement, but pass in the old values from your form and update all columns at once.

azarcomp SSC Rookie Points: 36 More actions · Answer 8

Thanks SSComitted. I just returned from a tour in Iraq as a reservist(SGT Spencer - 99th RSC battalion). We thank you for the support.

Anyway, to all others, super thanks for the help.

"If u don't have anything nice to say, dont say anything at all."

azarcomp SSC Rookie Points: 36 More actions · Answer 9

Steve, That is exactly what I was looking for.

I think the person who may hurtfull comments to me should be banned.

I am picking up my IT career as fast as I can, this new company is being kind to me becuase of my time away, I have 2 weeks so get it back. I am working around the clock. I used to train and consult SQL server around the country and consult it for 4 years, and yes, the time off makes me feel inexperienced again. But I did not fail my country, I will not fail my job, and my passion for t-sql I will never loose.

This will be repetative once I get it right. we are developing software and each form will have the same needs. Populate the controls, and

save/update and delete rows from sql tables.

your code is what is was looking for . thanks.:-):-P:-P:-P

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

azarcomp (7/12/2010)
I think the person who may hurtfull comments to me should be banned.

I don't see anyone been hurtful or nasty. I see a couple of direct, straightforward comments, but nothing nasty.

I used to train and consult SQL server around the country and consult it for 4 years, and yes, the time off makes me feel inexperienced again.

No offense intended, but if you trained and consulted in SQL for 4 years, a multi-column update should be utterly trivial. It's one of the fundamentals that one should not be able to forget. I last worked with VB 6 years ago, but I still remember the fundamentals of the language

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

rvasanth SSC Eights! Points: 873 More actions · Answer 11

Also you can do like below:

update table set col1=isnull(@newvalue1,col1), col2=isnull(@newvalue2,col2),etc...

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 12

Thanks for your service.

I don't think those comments were inappropriate, and if they were hurtful, perhaps you should re-examine what you are asking. An update is a fairly trivial statement. Perhaps you don't remember an UPDATE can only update one table, not multiple ones, but writing a simple UPDATE statement is something most people working with SQL Server learn to do in the first few days.

Good luck, we are happy to help, but we do expect you to do some work and not to mis-lead us or mis-represent yourself. Humility, professionalism, go a long way.

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 13

rvasanth (7/12/2010)
Also you can do like below:
update table set col1=isnull(@newvalue1,col1), col2=isnull(@newvalue2,col2),etc...

Don't forget that if your UPDATE statement does not have a WHERE clause, you will be updating every record.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

SQLRNNR SSC Guru Points: 281334 More actions · Answer 14

Alvin Ramard (7/12/2010)
rvasanth (7/12/2010)
Also you can do like below:
update table set col1=isnull(@newvalue1,col1), col2=isnull(@newvalue2,col2),etc...
Don't forget that if your UPDATE statement does not have a WHERE clause, you will be updating every record.

Running an update without a where clause could be a very big headache. So please do as Alvin suggested.

I would also recommend wrapping it in a transaction.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events