Stored Procedure select from multiple tables, variables

Posted on 2007-10-01
Last Modified: 2010-03-19

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


Question by:jasonmallison
    LVL 8

    Expert Comment

    create proc myProcedure

       join tbl_poHeader on numbersColumn = vendorID
    LVL 17

    Expert Comment

    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
    SELECT v.VendorName, v.Address, v.City, v.State,
        FROM tbl_poVendors v
       JOIN tbl_poHeader h
         ON h.VendorID = v.VendorID
        AND h.poRequisitionNumber = v.poRequisitionNumber

    LVL 8

    Expert Comment

    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.

    LVL 1

    Author Comment

    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.
    LVL 15

    Expert Comment

    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);
    LVL 1

    Author Comment

    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?
    LVL 8

    Accepted Solution

    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

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

    LVL 8

    Expert Comment

    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.

    LVL 1

    Author Comment

    Mr. Robot this looks like exactly what I want, thanks!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Training Course: Java/J2EE and SOA

    This course will cover both core and advanced Java concepts like Database connectivity, Threads, Exception Handling, Collections, JSP, Servlets, XMLHandling, and more. You'll also learn various Java frameworks like Hibernate and Spring.

    by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
    Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
    This video discusses moving either the default database or any database to a new volume.
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    779 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

    10 Experts available now in Live!

    Get 1:1 Help Now