?
Solved

Stored Procedure select from multiple tables, variables

Posted on 2007-10-01
9
Medium Priority
?
359 Views
Last Modified: 2010-03-19
Hi,

I have to tables, well I actually have more,  but only 2 that matter for this question. One is tbl_poVendors, and the other is tbl_poHeader. I am trying to select the vendor name, address, city, state, zip from the tbl_poVendors, where the poRequisitionNumber, and vendorid match certain numbers in the tbl_poHeader table. Can someone help me? I want to do this in a stored procedure if possible, and I would like to avoid creating a view (if I don't need to) if so, then so be it. Are there any pros/cons to a view? Any help would be MUCH appreciated...

Regards...

Jason
0
Comment
Question by:jasonmallison
9 Comments
 
LVL 8

Expert Comment

by:MrRobot
ID: 19994805
create proc myProcedure
as

select
   vendor_name,
   address,
   city,
   state,
   zip
from
   tbl_poVendors
   join tbl_poHeader on numbersColumn = vendorID
0
 
LVL 17

Expert Comment

by:Daniel Reynolds
ID: 19994820
You can use the following as a simple sproc to get started and later add variables to it if you are trying to find a specific po or vendor


CREATE procedure dbo.myNewStoredProc
AS
SELECT v.VendorName, v.Address, v.City, v.State, v.zip
    FROM tbl_poVendors v
   JOIN tbl_poHeader h
     ON h.VendorID = v.VendorID
    AND h.poRequisitionNumber = v.poRequisitionNumber

GO
0
 
LVL 8

Expert Comment

by:MrRobot
ID: 19994825
views are simpler, just results from a query, they can be used in nearly everywhere a table can. they can be indexed, so may get faster for static data. but if you want to give parameters, or make more than one operation, stored procedures are the way to go.

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 1

Author Comment

by:jasonmallison
ID: 19995033
Maybe I didn't ask correctly. I got my query analyzer to accept my storedprocedure, here is my select statement....

SELECT V.vendorName, V.vendorAddress1, V.vendorAddress2, V.vendorCity, V.vendorState, V.vendorZip from tbl_poVendors V, tbl_poHeader H where (H.vendorId = @vendorId) and (H.poRequisitionNumber = @poRequisitionNumber);

what I am trying to do is return the vendor name, address etc. based on the vendorId found in tbl_poHeader corresponding to my poRequisitionNumber also found in tbl_poHeader. BTW, my poRequisitionNumber is stored in session state.... not sure if that matters... It is also my identity field if that matters.
0
 
LVL 15

Expert Comment

by:danrosenthal
ID: 19995368
You are missing the join condition. Your query should look something like this (I am guessing that you have a column named VendorID in tbl_poHeader:

SELECT V.vendorName, V.vendorAddress1, V.vendorAddress2, V.vendorCity, V.vendorState, V.vendorZip
from tbl_poVendors V
inner join tbl_poHeader H
on V.vendorId = H.vendorId
where (H.vendorId = @vendorId) and (H.poRequisitionNumber = @poRequisitionNumber);
0
 
LVL 1

Author Comment

by:jasonmallison
ID: 19995406
Hi Dan,

So this is just a join that is done in memory? It doesn't affect my actual tables correct? Sorry, if this sounds like such a newbie question. And yes, vendorId is a field in my tbl_poHeader table. Is this similar to a view?
0
 
LVL 8

Accepted Solution

by:
MrRobot earned 1000 total points
ID: 19995421
this is what you want sounds like, but if the query you have typed above works for you, then you can simply change it like this example, it has input parameters, output parameters, and assign to output parameters using a given input parameter.

create proc myProc
  @vendorID int,
  @porequisitionnumber int,
  @vendorname varchar(50) out,
  @vendoraddress1 varchar(50) out,
  @vendoraddress2 varchar(50) out,
  @vendorcity varchar(50) out,
  @vendrostate varchar(50) out,
  @vendrozip varchar(50) out
as

select
  @vendorname = V.vendorName,
  @vendoraddress1 = V.vendorAddress1,
  @vendoraddress2 = V.vendorAddress2,
  @vendorcity = V.vendorCity,
  @vendorstate = V.vendorState,
  @vendorzip = V.vendorZip
from
  tbl_poVendors V
  join tbl_poHeader H on H.vendorID = v.vendorID
where
  H.poRequisitionNumber = @poRequisitionNumber
  and h.vendorID = @vendorID

0
 
LVL 8

Expert Comment

by:MrRobot
ID: 19995438
join operation doesn't modify your data, it is a statement that tells the sql server engine to use the combined tables for output.

it's not like view, views can't take parameters, they work like a table.

0
 
LVL 1

Author Comment

by:jasonmallison
ID: 19995440
Mr. Robot this looks like exactly what I want, thanks!
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

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.
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

809 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