We help IT Professionals succeed at work.

Stored Procedure select from multiple tables, variables

388 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
Comment
Watch Question

Commented:
create proc myProcedure
as

select
   vendor_name,
   address,
   city,
   state,
   zip
from
   tbl_poVendors
   join tbl_poHeader on numbersColumn = vendorID
Daniel ReynoldsSoftware Applications Developer / Integrator
CERTIFIED EXPERT

Commented:
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

Commented:
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.

Author

Commented:
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.
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);

Author

Commented:
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?
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Commented:
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.

Author

Commented:
Mr. Robot this looks like exactly what I want, thanks!
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.