Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Stored Procedure update multiple Row

Author  Topic 

timsweet
Starting Member

31 Posts

Posted - 2005-08-16 : 14:47:58
Thank you in advance for any assistance.

I need to update multiple rows with one stored procedure in the same table.

I haven't code one of these before.

The Coldfusion page will be passing me 6 row

@id, @Type, @Details, @PrimaryContact

My update query will be

Set Details = @detials, PrimaryContact = @PrimaryContacts
Where id=@id and Type = @type,

How do I handle the multiple row?

Tim

Thrasymachus
Constraint Violating Yak Guru

483 Posts

Posted - 2005-08-16 : 14:53:02
an update query will update all of the rows that meet your criteria. and they look like so...

UPDATE MyTable SET Col1 = @variable1,Col2 = @variable2
WHERE Col3 = @variable3
AND Col4 = @variable4

To see more examples go to Start--> Programs --> Microsoft SQL Server --> SQL Server Books Online --> Contents --> Transact SQL Reference --> UPDATE.



====================================================
Regards,
Sean Roussy

Thank you, drive through
Go to Top of Page

SamC
White Water Yakist

3467 Posts

Posted - 2005-08-16 : 14:58:39
Call the stored proc 6 times?

If you want to call the stored proc once and update 6 (or N) rows, how will you pass "N" rows of data in the paramter list?

CSV would be my choice.

Sort out the passing of N rows in a parameter list using csv.

Go to Top of Page

timsweet
Starting Member

31 Posts

Posted - 2005-08-16 : 15:36:30
Thanks.

Question: What is a CVS?

Additional Information:

From the web page I'll be getting

Id, WorkPhone, 5555-5555-555, 1
id, HomePhone, 555-555-5555, 0
id, WorkEmail, there@here.com, 1
id, Homeemail, Notthere@here.com, 0
Each is one row in the same table.

I've now just found out that one of the rows supplied from the page might not be in the table. Now it looks like I'll have to check to see if the row exist and if not do an insert...can that be done in one Stored Proc????? HELP!!!!

Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2005-08-16 : 16:19:27
Tim,

CVS is a source control system, but CSV means Comma Separated Values.

So, Sam's question is, how is the information being passed form the web page to the stored procedure? Will it look like your example, with commas between fields and carriage returns at the end, all in one parameter, as if it were a text file? Or will it have 4 parameters: A list of IDs, a list of Types, a list of Details and a list of Primary Flags, that will all have to be reconstructed to look like a set of rows?

And yes, you can do the check for existance and then INSERT or UPDATE all in one stored procedure, but not all in one SQL command. A stored procedure can have conditional branching, looping, and the other normal constructs of a programming language.

---------------------------
EmeraldCityDomains.com
Go to Top of Page
   

- Advertisement -