[dba-SQLServer]Sproc - Selecting from select statement

Maybe it would help if you would explain more about why you are trying to do
this.  How is "@qs" declared and what are you trying to do with it?
 
Doris Manning
Database Administrator
Hargrove Inc.
www.hargroveinc.com <http://www.hargroveinc.com/> 

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of David
Emerson
Sent: Wednesday, September 10, 2003 9:19 PM
To: dba-SQLServer at databaseadvisors.com
Subject: [dba-SQLServer]Sproc - Selecting from select statement


I am trying to use a select statement as the source of another select
statement in a Sproc.

The following statement works -

SELECT GateIDNo, MAX(MDQ) AS MaxMDQ, MAX(MDQDate) AS OldMDQDate
        FROM dbo.tblGateStationMDQ
        WHERE MDQ <> 0  
        GROUP BY GateIDNo
        HAVING (MAX(MDQDate) < '20031001')

But when I do the following -

SELECT @qs =    'SELECT GateIDNo, MaxMDQ, OldMDQDate
        FROM (SELECT GateIDNo, MAX(MDQ) AS MaxMDQ, MAX(MDQDate) AS
OldMDQDate
                        FROM dbo.tblGateStationMDQ
                        WHERE MDQ <> 0  
                        GROUP BY GateIDNo
                        HAVING (MAX(MDQDate) < ''20031001''))'

I get the following error -

Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near ')'.

Using Print (@qs) I get -

SELECT GateIDNo, MaxMDQ, OldMDQDate
        FROM (SELECT GateIDNo, MAX(MDQ) AS MaxMDQ, MAX(MDQDate) as
OldMDQDate
                        FROM dbo.tblGateStationMDQ
                        WHERE MDQ <> 0  
                        GROUP BY GateIDNo
                        HAVING (MAX(MDQDate) < '20031001'))

Any suggestions?


Regards

David Emerson
DALYN Software Ltd
25b Cunliffe St, Johnsonville
Wellington, New Zealand
Ph/Fax (877) 456-1205 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/dba-sqlserver/attachments/20030911/08ebf96a/attachment.html>