Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server ASP
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
SQL Server ASP Discussions about ASP programming with Microsoft's SQL Server. For more ASP forums, see the ASP forum category.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server ASP section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old July 6th, 2003, 10:00 PM
Authorized User
 
Join Date: Jul 2003
Location: , , Canada.
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
Default Stored Procedure -- Nested queries

I'm currently converting Access 2000 queries to SQL Server Views and Stored Procedures. The Reports I'm working with are fairly detailed, resulting in numerous nested queries. How would I do this from within a Stored Procedure, is it possible? (Or would I setup 4 Stored Procedures that reference one another?) Or .. is there a better way of handling this? Thanks.

CREATE PROCEDURE ....
SELECT ... etc. --> Output as Temp1
SELECT Temp1 ... --> Output as Temp2
SELECT Temp2 ...

Reply With Quote
  #2 (permalink)  
Old July 6th, 2003, 10:20 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Sydney, NSW, Australia.
Posts: 111
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi,

You could use Temp tables/variables. You could reference one sproc from another.

Without having some idea of what you're trying to achieve, there's no concrete advice we can give you.

Cheers
Ken

www.adOpenStatic.com
Reply With Quote
  #3 (permalink)  
Old July 6th, 2003, 11:08 PM
Authorized User
 
Join Date: Jul 2003
Location: , , Canada.
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Here's an example of what I'm trying to do ...
  1) SELECT transactions from FILE1 for a certain Period (Parameter input)
  2) GROUP output 1) by Date by Location + calc MAX values
  3) Pickup PRODUCTION Values by Location from FILE2
  4) Group Averages by Location from FILE1
  5) JOIN all files by Location to FILE3
Not sure whether I can do all this in 1 SPROC or whether I need to split the procedures. Also how do I call a procedure from within a SPROC given there are Input Parameters?

Reply With Quote
  #4 (permalink)  
Old July 7th, 2003, 06:53 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Sydney, NSW, Australia.
Posts: 215
Thanks: 0
Thanked 0 Times in 0 Posts
Default

It sounds like a sigle select statement would do the job. You might need to use correlated subqueries and derived tables, but it sounds relatively straightforward. Could you post the table structure?

regards
David Cameron
Reply With Quote
  #5 (permalink)  
Old July 8th, 2003, 02:03 AM
Authorized User
 
Join Date: Jul 2003
Location: , , Canada.
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
Default

This is what I have so far ...
Query 1 ...
@XFrom datetime, @XTo datetime AS
SELECT SLocnCode, SStartDate, SKey, STestCode,
       QValue = Max(CASE WHEN SValue is Null then 0 else SValue END),
FROM tblSamples
WHERE (SStartDate Between @XFrom And @XTo) AND (SType = 'A')
GROUP BY SLocnCode, SStartDate, SKey, STestCode
HAVING STestCode IN('Test1','Test2' etc.)
ORDER BY SLocnCode, SStartDate

Query 2 ...
SELECT SStartDate, SLocnCode,
  ZTest1 = Max(CASE WHEN STestCode = 'Test1' Then QValue Else 0 END),
  ZTest2 = Max(CASE WHEN STestCode = 'Test2' Then QValue ELse 0 END)
FROM Query1
GROUP BY SStartDate, SLocnCode
ORDER BY SStartDate

Query 3 ...
SELECT SLocnCode,
     ZProd = Max(CASE WHEN STestCode = 'PROD' Then SValue else 0 END)
FROM tblSamples
WHERE STestCode = 'PROD' AND SStartDate Between @XFrom And @XTo
GROUP BY SLocnCode
ORDER BY SLocnCode

Query4 ...
SELECT SLocnCode, Avg(Test2) AS ZAvg
FROM Query2
GROUP BY SLocnCode

Query 5 ...
SELECT tblSortSeq.SrtSeq, tblLocn.LocnDesc, Query2.SStartDate,
   Query3.ZProd, Query2.ZTest1 ..., Query4.ZAvg
FROM tblLocn LEFT JOIN Query2 ON tblLocn.LocnCode = Query2.SLocnCode
LEFT JOIN Query3 ON tblLocn.LocnCode = Query3.SLocnCode)
LEFT JOIN tblSortSeq ON tblLocn.LocnCode = tblSortSeq.SrtLocnCode) LEFT JOIN Query4 ON tblLocn.LocnCode = Query4.SLocnCode
WHERE (tblLocn.LocnCode)='Locn1' etc.)
ORDER BY tblSortSeq.SrtSeq, tblLocn.LocnCode, tblLocn.LocnDesc, Query2.SStartDate

Before switching to SQL Server, I found that splitting the queries made quite a difference in run time.

Reply With Quote
  #6 (permalink)  
Old July 8th, 2003, 02:29 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Sydney, NSW, Australia.
Posts: 215
Thanks: 0
Thanked 0 Times in 0 Posts
Default

You can do this using views (possibly) or derived/temp tables (definitely). A derived table is simply sticking a SELECT statement in the from clause, giving it an alias and treating it as a table. It would probably be better to rewrite this rather than using that many derived tables.

I haven't got the time to look at it properly right now and I'm not sure how each of the columns are used, but a few quick questions:
1. If you change the second, third and fourth querys to all query the table directly (and messing around with the WHERE clause a little) do you the get the same results?
2. If 1 is true, can you combine queries 1-4 into one (or any two of the queries into one)? In other words this is basically a question on what the effect of the GROUP BY clause is.

If you do create the query using derived tables, remove the ORDER BY clauses for queries 1-4. They will have no effect and may slow things down.

regards
David Cameron
Reply With Quote
  #7 (permalink)  
Old July 8th, 2003, 03:12 AM
Authorized User
 
Join Date: Jul 2003
Location: , , Canada.
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Well .. the tricky part is that I need to pickup the MAXIMUM value for the Day, and then need an Average for the Period, based on the Maximum for the Day. If I can do that in 1 procedure without the WHERE clause effecting my outputs, that would be great. As far as creating views, I don't believe I can pass Parameters, which is why I have opted to go with SPROC's.

Reply With Quote
  #8 (permalink)  
Old July 11th, 2003, 02:46 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Sydney, NSW, Australia.
Posts: 215
Thanks: 0
Thanked 0 Times in 0 Posts
Default

OK, I have a few spare moments to write out something for you so here goes:

Briefly where possible I have moved the queries into derived tables (the Selects in the FROM clause) and combined them. This wasn't possible for the results of query2, which had to be dropped into a temp table.

I've used your pseudo code, so it won't work, but this should point you in the right direction.
Code:
INSERT INTO #Part1
SELECT SStartDate, SLocnCode,
ZTest1 = Max(CASE WHEN STestCode = 'Test1' Then QValue Else 0 END),
ZTest2 = Max(CASE WHEN STestCode = 'Test2' Then QValue ELse 0 END)
FROM (SELECT SLocnCode, SStartDate, SKey, STestCode,
      QValue = Max(CASE WHEN SValue is Null then 0 else SValue END),
      FROM tblSamples
      WHERE (SStartDate Between @XFrom And @XTo) AND (SType = 'A')
      GROUP BY SLocnCode, SStartDate, SKey, STestCode
      HAVING STestCode IN('Test1','Test2' etc.)
     )

SELECT tblSortSeq.SrtSeq, tblLocn.LocnDesc, Q1StartDate, Q2.ZProd,
    Q1.ZTest1, Q3.ZAvg
FROM tblLocn L
    INNER JOIN #Part1 AS Q1 ON
    Q1.LocnCode = L.LocnCode
    INNER JOIN (SELECT SLocnCode,
                     ZProd = Max(CASE WHEN STestCode = 'PROD' Then SValue else 0 END)
                 FROM tblSamples
                 WHERE STestCode = 'PROD' AND SStartDate Between @XFrom And @XTo
                 GROUP BY SLocnCode
                 ORDER BY SLocnCode
                ) AS Q2 ON
    Q2.LocnCode = L.LocnCode     
    INNER JOIN tblSortSeq ON
    tblLocn.LocnCode = tblSortSeq.SrtLocnCode
    INNER JOIN (SELECT SLocnCode, AVG(Test2) ZAvg
                FROM #Part1
                GROUP BY SLocnCode
               ) AS Q3 ON
    Q3.SLocnCode = L.LocnCode

DROP TABLE #Part1
regards
David Cameron
Reply With Quote
  #9 (permalink)  
Old February 28th, 2004, 07:55 PM
Authorized User
 
Join Date: Jul 2003
Location: , , Canada.
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Never did thank you for helping me out with this one. Thanks for taking the time.

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Nested Repeater Using Stored Procedure kwilliams ASP.NET 2.0 Professional 1 July 21st, 2008 06:49 PM
Nested Calculated Fields in Queries SerranoG Access 0 August 14th, 2006 10:04 AM
Converting Access Queries Into Stored Procedures markw SQL Language 1 March 15th, 2005 11:49 AM
Complex (to me) nested queries spanning 4 tables! bobburke SQL Language 1 April 23rd, 2004 10:54 AM
Pass-through queries vs stored procedures kendawg Access 2 October 8th, 2003 02:00 AM



All times are GMT -4. The time now is 02:22 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.