Solved

SQL Server 2008 Stored Procedure - updating multiple tables

Posted on 2010-09-19
4
774 Views
Last Modified: 2012-05-10
I have a stored procedure that updates the data in a table based on input values. Once that part is done, I want the procedure to take one of the input parameters and return a record from a view, a different recordset. Using the data returned from the view, I want to update another table. Is there a way to do this?
0
Comment
Question by:dodgerfan
  • 2
  • 2
4 Comments
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33713419
The information is so vague, hope this helps.
Steps 2 and 3 can be done in one query, instead of retrieving and storing it temporarily.
create proc myprocedure

@input1 int,

@input2 varchar(20),

@input3 int,

@key uniqueidentifier,

@key2 int

AS

set nocount on;



-- update a table

update tbl1 set col1 = @input1 where primary_key = @key;



-- update another table using values from "a different recordset"

update tbl3 set

	colA = v.sourcecol1,

	colB = v.sourcecol2

from view_record v

where v.col2 = @input2

  and tbl3.primary_key = @key2;

GO

Open in new window

0
 

Author Comment

by:dodgerfan
ID: 33713460
Yes, sorry about the vagueness. I should have been more detailed. My current sp looks lke this:
create procedure [dbo].[returnBook]
@rentid int,
@memberId int,
@returneddate datetime,
@bookid Int
AS
UPDATE bookrental
SET
returneddate=@returneddate
WHERE rentId=@rentalId And memberId = @memberId

This works. Now I need to open another query to find the next book for that member, call vwNextBook with the memberid being the value. Then I need to take that data and insert into the bookrental table, with some of the data already captured in the original variables, too. So take the bookId returned in this query and insert it with memberid and a new date into the oriiginal bookrental table. I think this makes sense. I will try what you have already posted, too.
0
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 500 total points
ID: 33713599
I'm thinking something like this

create procedure [dbo].[returnBook]
@rentid int,
@memberId int,
@returneddate datetime,
@bookid Int
AS
UPDATE bookrental
SET
returneddate=@returneddate
WHERE rentId=@rentid And memberId = @memberId

insert bookrental (bookID, memberId, rentId, rentdate)
select top(1) bookID, @memberId, @rentId, getdate() -- mix of values from vwNextBook and variables
from vwNextBook where memberId = @memberId
0
 

Author Closing Comment

by:dodgerfan
ID: 33713717
Thanks for the help. I got it working right with your help.
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now