SQL Query in Stored Proc - SQL Server Forums

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)

SQL Query in Stored Proc

Author

Topic

skamaster
Starting Member

1 Post

Posted - 2007-03-02 : 17:36:26

I get the following error in Classic ASP when I execute a stored proc with the ASP page:

Microsoft OLE DB Provider for SQL Server error '80040e14'

Column 'SurveyHeaderSummary.SID' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.

/surveys/itcss_2006/reports.asp, line 123

The contents of the query in the stored proc is:

CREATE PROCEDURE SurveyGetHeaderSummary (
@SID int,
@strFilter_1 nvarchar(4000) = '',
@strFilter_2 nvarchar(4000) = ''
) AS

DECLARE @strQuery nvarchar(4000)
SET @strQuery =
'SELECT ISNULL(SurveyHeaderSummary.SID, SurveyHeaders.SID) AS SID, ISNULL(SurveyHeaderSummary.HID, SurveyHeaders.HID) AS HID,
ISNULL(SurveyHeaderSummary.HName, SurveyHeaders.HName) AS HName, ISNULL(SurveyHeaderSummary.HType, SurveyHeaders.HType)
AS HType, ISNULL(SurveyHeaderSummary.HOrder, SurveyHeaders.HOrder) AS HOrder, ISNULL(SurveyHeaderSummary.HOID,
SurveyHeaderOptions.HOID) AS HOID, ISNULL(SurveyHeaderSummary.HOText, SurveyHeaderOptions.HOText) AS HOText,
ISNULL(SurveyHeaderSummary.HOValue, SurveyHeaderOptions.HOValue) AS HOValue, ISNULL(SurveyHeaderSummary.HOOrder,
SurveyHeaderOptions.HOOrder) AS HOOrder, COUNT(SurveyHeaderSummary.RID) AS RIDs
FROM SurveyHeaderSummary' + @strFilter_1 + ' FULL OUTER JOIN
SurveyHeaderOptions ON SurveyHeaderSummary.SID = SurveyHeaderOptions.SID AND SurveyHeaderSummary.HID = SurveyHeaderOptions.HID AND
SurveyHeaderSummary.HOID = SurveyHeaderOptions.HOID LEFT OUTER JOIN
SurveyHeaders ON SurveyHeaderOptions.SID = SurveyHeaders.SID AND SurveyHeaderOptions.HID = SurveyHeaders.HID
GROUP BY ISNULL(SurveyHeaderSummary.SID, SurveyHeaders.SID), ISNULL(SurveyHeaderSummary.HID, SurveyHeaders.HID),
ISNULL(SurveyHeaderSummary.HName, SurveyHeaders.HName), ISNULL(SurveyHeaderSummary.HType, SurveyHeaders.HType),
ISNULL(SurveyHeaderSummary.HOrder, SurveyHeaders.HOrder), ISNULL(SurveyHeaderSummary.HOID, SurveyHeaderOptions.HOID),
ISNULL(SurveyHeaderSummary.HOText, SurveyHeaderOptions.HOText), ISNULL(SurveyHeaderSummary.HOValue, SurveyHeaderOptions.HOValue),
ISNULL(SurveyHeaderSummary.HOOrder, SurveyHeaderOptions.HOOrder)
HAVING ISNULL(SurveyHeaderSummary.SID, SurveyHeaders.SID) = ' + CAST(@SID AS nvarchar) + @strFilter_2 + '
ORDER BY ISNULL(SurveyHeaderSummary.HOrder, SurveyHeaders.HOrder), ISNULL(SurveyHeaderSummary.HOOrder, SurveyHeaderOptions.HOOrder)'

EXEC sp_executesql @strQuery
GO
---

Any help you can provide is very much appreciated. I am stuck, since it appears to this developer that the column it is complaining about IS in the group by clause.

Skamaster

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-02 : 17:47:00

1) Before "EXEC sp_executesql @strquery", please add a PRINT @strquery
2) run the code
3) Post here the result of the above PRINT statement.

Peter Larsson
Helsingborg, Sweden

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2007-03-02 : 17:53:08

use
WHERE
not
HAVING
in this case...

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources