Problems with coordinating a stored procedure to output results for each row

Question

Problems with coordinating a stored procedure to output results for each row

Zososql

Ten Centuries

Points: 1212
More actions
January 17, 2017 at 1:23 pm

#317180

I have this stored procedure that needs to return a compounded resultset from 3 difference sql queries
alter procedure as [myreport] (@starting date,@ending date)
AS
BEGIN
SELECT O.orderID
   ,H.dateordered
   ,O.secondaryID
   ,O.custname
   FROM Orders as O,
   OrderHeaders as H
   WHERE
   dateordered between @starting AND @ending

AND O.secondaryID = H.secondaryID
     GROUP BY
     O.orderID
   ,H.dateordered
   ,O.secondaryID
   ,O.custname
     ORDER BY
             H.dateordered
END
...so this renders both an orderID and secondaryID . Predicated on each row in this resultset , I need to grab that orderID and input it into this sql statement:
SELECT arrivedate FROM orderauxiliary
        WHERE
        mode = 'action taken'

                     and orderID = O.orderID
        and descrip LIKE '%time of action%' ;

...and also for each row in the main resulset I need to append it to the application's resultset
SELECT actiondate FROM comments
        WHERE
        modetype = 'A1'
        and secondaryID = H.dateordered

        So along with the resultset of the first sql statement/query above, the arrivedate and the actiondate has to be included as this application will display each line (orderid, secondaryid, custname, etc) on a web interface with the corresponding arrivedate and actiondate . For some reason I cannot Declare a @ variable and assign it to the last 2 sql statements for O.orderID and H.dateordered.
        ?? thanks in advance

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286573 More actions · Answer 1

Pretty sure you just need a couple extra joins here to make this work. Also, please notice that I created an explicit inner join between Order and OrderHeaders. This style of join has been around for nearly 30 years now and should be used in favor of the ANSI-89 style joins as posted.

SELECT O.orderID , H.dateordered , O.secondaryID , O.custname , oa.arrivedate , c.actiondate FROM Orders as O join OrderHeaders as H on O.secondaryID = H.secondaryID join OrderAuxiliary oa on oa.orderID = o.orderID and mode = 'action taken' and descrip like '%time of action%' join comments c on c.secondaryID = H.dateordered and c.modetype = 'A1' WHERE O.dateordered between @starting AND @ending GROUP BY O.orderID , H.dateordered , O.secondaryID , O.custname ORDER BY H.dateordered

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Zososql Ten Centuries Points: 1212 More actions · Answer 2

Sean Lange - Tuesday, January 17, 2017 1:48 PM
Pretty sure you just need a couple extra joins here to make this work. Also, please notice that I created an explicit inner join between Order and OrderHeaders. This style of join has been around for nearly 30 years now and should be used in favor of the ANSI-89 style joins as posted.
SELECT O.orderID , H.dateordered , O.secondaryID , O.custname , oa.arrivedate , c.actiondate FROM Orders as O join OrderHeaders as H on O.secondaryID = H.secondaryID join OrderAuxiliary oa on oa.orderID = o.orderID and mode = 'action taken' and descrip like '%time of action%' join comments c on c.secondaryID = H.dateordered and c.modetype = 'A1' WHERE O.dateordered between @starting AND @ending GROUP BY O.orderID , H.dateordered , O.secondaryID , O.custname ORDER BY H.dateordered

I believe this is on the right track, thanks a ton ! I was also considering CTE parsing, or maybe if a CURSOR was the right way to deal with each row for the result, but I'll continue testing.
Zo

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

Zososql - Tuesday, January 17, 2017 1:23 PM

You should also look into CROSS/OUTER APPLY. This scenario is one of it's main uses.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service