?
Solved

SQL Server 2008 Stored Procedure - updating multiple tables

Posted on 2010-09-19
4
Medium Priority
?
794 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

650 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