Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 806
  • Last Modified:

SQL Server 2008 Stored Procedure - updating multiple tables

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
dodgerfan
Asked:
dodgerfan
  • 2
  • 2
1 Solution
 
cyberkiwiCommented:
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
 
dodgerfanAuthor Commented:
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
 
cyberkiwiCommented:
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
 
dodgerfanAuthor Commented:
Thanks for the help. I got it working right with your help.
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now