Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2006
    Posts
    82

    Unanswered: Stored Proc Select Problem

    hi all

    i have a simple problem that i cant get through.

    i am writing a stored procedure and i want to make this select statement

    Code:
    SELECT     TOP 100 PERCENT dbo.tPA00175.chrJobNumber, dbo.tPA20802.dteDocumentDate, dbo.tPA00002.chrPhaseName, dbo.tPA00007.chrEmployeeNumber, 
                          dbo.tPA20802.numActualQuantity, dbo.tPA20802.numChargeOutRate, dbo.tPA20801.numTotalCharges, dbo.tPA00125.numQTYInvoiced
    FROM         dbo.tPA00125 INNER JOIN
                          dbo.tPA00007 ON dbo.tPA00125.intEmployeeKey = dbo.tPA00007.intEmployeeKey INNER JOIN
                          dbo.tPA20802 ON dbo.tPA00125.intJobLineKey = dbo.tPA20802.intJobLineKey INNER JOIN
                          dbo.tPA20801 ON dbo.tPA20802.intTimesheetKey = dbo.tPA20801.intTimesheetKey INNER JOIN
                          dbo.tPA00002 ON dbo.tPA00125.intPhaseKey = dbo.tPA00002.intPhaseKey INNER JOIN
                          dbo.tPA00175 ON dbo.tPA00125.intJobKey = dbo.tPA00175.intJobKey
    WHERE     (dbo.tPA00125.numQTYInvoiced = 0)
    ORDER BY dbo.tPA00175.chrJobNumber DESC
    how would i go about setting my variables, if at all possible, from the values im calling for in the select statement?

    is there another way of doing it rather than this

    Code:
    set @Phase=(select dbo.tPA00002.chrPhaseName 
    FROM         dbo.tPA00125 INNER JOIN
                          dbo.tPA00007 ON dbo.tPA00125.intEmployeeKey = dbo.tPA00007.intEmployeeKey INNER JOIN
                          dbo.tPA20802 ON dbo.tPA00125.intJobLineKey = dbo.tPA20802.intJobLineKey INNER JOIN
                          dbo.tPA20801 ON dbo.tPA20802.intTimesheetKey = dbo.tPA20801.intTimesheetKey INNER JOIN
                          dbo.tPA00002 ON dbo.tPA00125.intPhaseKey = dbo.tPA00002.intPhaseKey INNER JOIN
                          dbo.tPA00175 ON dbo.tPA00125.intJobKey = dbo.tPA00175.intJobKey
    WHERE     (dbo.tPA00125.numQTYInvoiced = 0))
    
    set @Resource=(select dbo.tPA00007.chrEmployeeName 
    FROM         dbo.tPA00125 INNER JOIN
                          dbo.tPA00007 ON dbo.tPA00125.intEmployeeKey = dbo.tPA00007.intEmployeeKey INNER JOIN
                          dbo.tPA20802 ON dbo.tPA00125.intJobLineKey = dbo.tPA20802.intJobLineKey INNER JOIN
                          dbo.tPA20801 ON dbo.tPA20802.intTimesheetKey = dbo.tPA20801.intTimesheetKey INNER JOIN
                          dbo.tPA00002 ON dbo.tPA00125.intPhaseKey = dbo.tPA00002.intPhaseKey INNER JOIN
                          dbo.tPA00175 ON dbo.tPA00125.intJobKey = dbo.tPA00175.intJobKey
    WHERE     (dbo.tPA00125.numQTYInvoiced = 0))
    
    set @hours=(select dbo.tPA20802.numActualQuantity 
    FROM         dbo.tPA00125 INNER JOIN
                          dbo.tPA00007 ON dbo.tPA00125.intEmployeeKey = dbo.tPA00007.intEmployeeKey INNER JOIN
                          dbo.tPA20802 ON dbo.tPA00125.intJobLineKey = dbo.tPA20802.intJobLineKey INNER JOIN
                          dbo.tPA20801 ON dbo.tPA20802.intTimesheetKey = dbo.tPA20801.intTimesheetKey INNER JOIN
                          dbo.tPA00002 ON dbo.tPA00125.intPhaseKey = dbo.tPA00002.intPhaseKey INNER JOIN
                          dbo.tPA00175 ON dbo.tPA00125.intJobKey = dbo.tPA00175.intJobKey
    WHERE     (dbo.tPA00125.numQTYInvoiced = 0))
    
    set @rate=(select dbo.tPA20802.numChargeOutRate 
    FROM         dbo.tPA00125 INNER JOIN
                          dbo.tPA00007 ON dbo.tPA00125.intEmployeeKey = dbo.tPA00007.intEmployeeKey INNER JOIN
                          dbo.tPA20802 ON dbo.tPA00125.intJobLineKey = dbo.tPA20802.intJobLineKey INNER JOIN
                          dbo.tPA20801 ON dbo.tPA20802.intTimesheetKey = dbo.tPA20801.intTimesheetKey INNER JOIN
                          dbo.tPA00002 ON dbo.tPA00125.intPhaseKey = dbo.tPA00002.intPhaseKey INNER JOIN
                          dbo.tPA00175 ON dbo.tPA00125.intJobKey = dbo.tPA00175.intJobKey
    WHERE     (dbo.tPA00125.numQTYInvoiced = 0))
    
    set @amount=(select dbo.tPA20801.numTotalCharges 
    FROM         dbo.tPA00125 INNER JOIN
                          dbo.tPA00007 ON dbo.tPA00125.intEmployeeKey = dbo.tPA00007.intEmployeeKey INNER JOIN
                          dbo.tPA20802 ON dbo.tPA00125.intJobLineKey = dbo.tPA20802.intJobLineKey INNER JOIN
                          dbo.tPA20801 ON dbo.tPA20802.intTimesheetKey = dbo.tPA20801.intTimesheetKey INNER JOIN
                          dbo.tPA00002 ON dbo.tPA00125.intPhaseKey = dbo.tPA00002.intPhaseKey INNER JOIN
                          dbo.tPA00175 ON dbo.tPA00125.intJobKey = dbo.tPA00175.intJobKey
    WHERE     (dbo.tPA00125.numQTYInvoiced = 0))
    
    set @date=(select dteDocumentDate 
    FROM         dbo.tPA00125 INNER JOIN
                          dbo.tPA00007 ON dbo.tPA00125.intEmployeeKey = dbo.tPA00007.intEmployeeKey INNER JOIN
                          dbo.tPA20802 ON dbo.tPA00125.intJobLineKey = dbo.tPA20802.intJobLineKey INNER JOIN
                          dbo.tPA20801 ON dbo.tPA20802.intTimesheetKey = dbo.tPA20801.intTimesheetKey INNER JOIN
                          dbo.tPA00002 ON dbo.tPA00125.intPhaseKey = dbo.tPA00002.intPhaseKey INNER JOIN
                          dbo.tPA00175 ON dbo.tPA00125.intJobKey = dbo.tPA00175.intJobKey
    WHERE     (dbo.tPA00125.numQTYInvoiced = 0))
    
    SET @invDate=(select(getdate()))

    thanks alot

    tibor

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd be inclined to use:
    Code:
    SELECT
       @Phase = dbo.tPA00002.chrPhaseName
    ,  @Resource = dbo.tPA00007.chrEmployeeName  
    ,  @hours = dbo.tPA20802.numActualQuantity
    ,  @rate = dbo.tPA20802.numChargeOutRate 
    ,  @amount = dbo.tPA20801.numTotalCharges
    ,  @date = dteDocumentDate
    ,  @invDate = GetDate()
       FROM  dbo.tPA00125
       INNER JOIN dbo.tPA00007
          ON dbo.tPA00125.intEmployeeKey = dbo.tPA00007.intEmployeeKey
       INNER JOIN dbo.tPA20802
          ON dbo.tPA00125.intJobLineKey = dbo.tPA20802.intJobLineKey
       INNER JOIN dbo.tPA20801
          ON dbo.tPA20802.intTimesheetKey = dbo.tPA20801.intTimesheetKey
       INNER JOIN dbo.tPA00002
          ON dbo.tPA00125.intPhaseKey = dbo.tPA00002.intPhaseKey
       INNER JOIN dbo.tPA00175
          ON dbo.tPA00125.intJobKey = dbo.tPA00175.intJobKey
       WHERE  0 = dbo.tPA00125.numQTYInvoiced
    -PatP

  3. #3
    Join Date
    Mar 2006
    Posts
    82
    great! thank you that works just perfectly. but unfortunately i just ran into another problem. how would i go about running it and getting information when it could have several values returned from the select statement. this is a great example that i will keep for future reference but right now its only returning me 1 row. how would i set it up to where i could get every row?

  4. #4
    Join Date
    Mar 2006
    Posts
    82
    Ive tried playing with a cursor but little did i know that i cant assign my values within the cursor. so now i am totally stumed on this

  5. #5
    Join Date
    Mar 2006
    Posts
    82
    nevermind, i just found the soloution. just run a regular select statement and fetch them into the variables inside the cursor. whew. thanks alot

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Just a word of warning... Cursors are the devil's best friend. They can be magical things when used correctly, but they're treacherous... They can scuttle performance to the point that you'll get answers to your problems easily, but only once you are too old to care!

    -PatP

  7. #7
    Join Date
    May 2006
    Posts
    16
    simple way:
    Create a temp table for your first query and then do join with your main table

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •