Multiple Select Query in a PROCEDURE

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Multiple Select Query in a PROCEDURE

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-04-02 : 09:13:29

Sabu writes "I have 4 diffrent Select Query, I need to put these in a procedure and then on my ASP page I am going to write a loop where I can retrive this...

SELECT Count(*)
FROM tblPurchaseOrder (NOLOCK)
WHERE OrderRefNo LIKE '0011%' AND InvoiceNumber IS NULL AND
EntryDate >= CONVERT(varchar, GETDATE(), 101)

SELECT COUNT(*)
FROM tblPurchaseOrder (NOLOCK)
WHERE OrderRefNo LIKE '0011%' AND InvoiceNumber IS NULL AND
DATEDIFF(minute, entryDate, GETDATE()) < 60

SELECT COUNT(*)
FROM tblPurchaseOrder (NOLOCK)
WHERE OrderRefNo LIKE '0011%' AND
InvoiceNumber IS NOT NULL AND
EntryDate >= CONVERT(varchar, GETDATE(), 101)

SELECT COUNT(*)
FROM tblPurchaseOrder (NOLOCK)
WHERE OrderRefNo LIKE '0011%' AND
InvoiceNumber IS NOT NULL AND DATEDIFF(minute,
entryDate, GETDATE()) < 60

Now these 4 Query will have diffrent field names.. Please Help"

Nazim
A custom title

1408 Posts

Posted - 2002-04-02 : 09:22:50

you can union them.

HTH

--------------------------------------------------------------

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-02 : 09:24:16

All you have to do is alias the Count(*) expression:

SELECT Count(*) Total
FROM tblPurchaseOrder (NOLOCK)
WHERE OrderRefNo LIKE '0011%' AND InvoiceNumber IS NULL AND
EntryDate >= CONVERT(varchar, GETDATE(), 101)

SELECT COUNT(*) Total
FROM tblPurchaseOrder (NOLOCK)
WHERE OrderRefNo LIKE '0011%' AND InvoiceNumber IS NULL AND
DATEDIFF(minute, entryDate, GETDATE()) < 60

SELECT COUNT(*) Total
FROM tblPurchaseOrder (NOLOCK)
WHERE OrderRefNo LIKE '0011%' AND
InvoiceNumber IS NOT NULL AND
EntryDate >= CONVERT(varchar, GETDATE(), 101)

SELECT COUNT(*) Total
FROM tblPurchaseOrder (NOLOCK)
WHERE OrderRefNo LIKE '0011%' AND
InvoiceNumber IS NOT NULL AND DATEDIFF(minute,
entryDate, GETDATE()) < 60

And, you can UNION these 4 statements together to get one recordset, and you won't have to loop anything:

SELECT Count(*) Total
FROM tblPurchaseOrder (NOLOCK)
WHERE OrderRefNo LIKE '0011%' AND InvoiceNumber IS NULL AND
EntryDate >= CONVERT(varchar, GETDATE(), 101)
UNION ALL
SELECT COUNT(*) Total
FROM tblPurchaseOrder (NOLOCK)
WHERE OrderRefNo LIKE '0011%' AND InvoiceNumber IS NULL AND
DATEDIFF(minute, entryDate, GETDATE()) < 60
UNION ALL
SELECT COUNT(*) Total
FROM tblPurchaseOrder (NOLOCK)
WHERE OrderRefNo LIKE '0011%' AND
InvoiceNumber IS NOT NULL AND
EntryDate >= CONVERT(varchar, GETDATE(), 101)
UNION ALL
SELECT COUNT(*) Total
FROM tblPurchaseOrder (NOLOCK)
WHERE OrderRefNo LIKE '0011%' AND
InvoiceNumber IS NOT NULL AND DATEDIFF(minute,
entryDate, GETDATE()) < 60

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources