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

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

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?

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

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.

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

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.

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.
regards
David Cameron

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