Problem with Stored Procedure - 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 2005 Forums

Transact-SQL (2005)

Problem with Stored Procedure

Author

Topic

wabaker95
Starting Member

12 Posts

Posted - 2008-09-02 : 11:42:02

Attempting to debug the following stored procedure. Error is

Msg 164, Level 15, State 1, Procedure REPORT_SalesWorks_General, Line 75
Each GROUP BY expression must contain at least one column that is not an outer reference.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[REPORT_SalesWorks_General]

@FID AS INTEGER,
@CampaignIDs AS VARCHAR(1024),
@ReportStartDate AS DATETIME,
@ReportEndDate AS DATETIME,
@UserStates AS VARCHAR(512),
@UserZIPs AS VARCHAR(2048),
@AgentGUIDs AS VARCHAR(8000),
@Status AS VARCHAR(64),
@Departments AS VARCHAR(2048),
@TellerBranches AS VARCHAR(2048),
@MemberBranches AS VARCHAR(2048),
@ShowInactive AS BIT,
@ExcludeHistorical AS BIT

AS

--SET @FID = 10002
--SET @CampaignIDs = '1005, 1006'
--SET @ReportStartDate = '03/01/05'
--SET @ReportEndDate = '05/01/05'
--SET @UserStates = ''
--SET @UserZIPs = '03049, 01752'
--SET @AgentGUIDs = '{38AE6A91-F98C-4AB9-BE2F-10F6F747ABE0},{3E83D2B8-5D3C-4C49-B116-A63D7ECA9529}'
--SET @Status = ''
--SET @Departments = '100, 101, 102, 103'
--SET @Branches = '49, 65, 38, 68'
--SET @ShowInactive = 0
--SET @ExcludeHistorical = 1

DECLARE @DefaultStatusSearch AS VARCHAR(32)

SET @DefaultStatusSearch = '0,1,2,3,4,5,6,7,8,9'

IF @UserStates = '' SET @UserStates = NULL
IF @UserZIPs = '' SET @UserZIPs = NULL
IF @AgentGUIDs = '' SET @AgentGUIDs = NULL
IF @Departments = '' SET @Departments = NULL
IF @Status = '' SET @Status = NULL
IF @Status IS NULL SET @Status = @DefaultStatusSearch

IF (SELECT OBJECT_ID('tempdb.dbo.#Campaign_Status','U')) IS NOT NULL
BEGIN
DROP TABLE #Campaign_Status
END

IF (SELECT OBJECT_ID('tempdb.dbo.#Teller_Branch','U')) IS NOT NULL
BEGIN
DROP TABLE #Teller_Branch
END

CREATE TABLE #Teller_Branch
(UserGUID UNIQUEIDENTIFIER,
BranchID INT,
StartDate DATETIME,
EndDate DATETIME,
PRIMARY KEY (UserGUID, BranchID, StartDate),
UNIQUE(UserGUID, BranchID, StartDate))

CREATE TABLE #Campaign_Status
(ItemID INT,
CampaignID INT,
StatusID INT,
MemberGUID UNIQUEIDENTIFIER,
MRMUserGUID UNIQUEIDENTIFIER,
LogDate DATETIME,
Active BIT,
Member_BranchID INT DEFAULT NULL,
Teller_BranchID INT DEFAULT NULL,
PRIMARY KEY (CampaignID, ItemID, StatusID, Active, LogDate),
UNIQUE (CampaignID, ItemID, StatusID, Active, LogDate))

INSERT INTO
#Teller_Branch
SELECT
User1.UserGUID,
User1.BranchID,
User1.StartDate,
(SELECT
MIN(User2.StartDate)
FROM
MRM_User_Branch User2 (NOLOCK)
WHERE
User1.UserGUID = User2.UserGUID AND
User2.StartDate > User1.StartDate
GROUP BY
User1.UserGUID)
FROM
MRM_User_Branch User1 (NOLOCK)
WHERE
User1.FID = @FID AND
User1.StartDate < @ReportStartDate

UPDATE
#Teller_Branch
SET
EndDate = DATEADD(YEAR, 1, GETUTCDATE())
WHERE
EndDate IS NULL

If @ExcludeHistorical = 0
BEGIN

INSERT INTO #Campaign_Status
SELECT
Sales_Campaign_Users.ItemID,
Sales_Campaign_Users.CampaignID,
IsNull(Sales_Campaign_Status_Log.StatusID, 0),
Sales_Campaign_Users.MemberGUID,
Sales_Campaign_Status_Log.MRM_UserGUID,
IsNull(Sales_Campaign_Status_Log.LogDate, dbo.Sales_Campaign_Users.AddDate),
IsNULL(Sales_Campaign_Status_Log.Active, 1),
Member.BranchID,
NULL
FROM
Sales_Campaign_Users (NOLOCK)
LEFT JOIN
Sales_Campaign_Status_Log (NOLOCK)
ON
Sales_Campaign_Users.ItemID = Sales_Campaign_Status_Log.ItemID
LEFT JOIN
Member (NOLOCK)
ON
Member.FID = @FID AND
Member.MemberGUID = Sales_Campaign_Users.MemberGUID
WHERE
(Sales_Campaign_Users.CampaignID IN (SELECT value FROM dbo.fn_Split(@CampaignIDs, ',')) AND
Sales_Campaign_Status_Log.LogDate > @ReportStartDate AND
Sales_Campaign_Status_Log.LogDate < @ReportEndDate)
OR
(dbo.Sales_Campaign_Users.CampaignID IN (SELECT value FROM dbo.fn_Split(@CampaignIDs, ',')) AND
dbo.Sales_Campaign_Users.StatusID IN (SELECT value FROM dbo.fn_Split_INTEGER(@Status, ',')) AND
dbo.Sales_Campaign_Users.AddDate > @ReportStartDate AND
dbo.Sales_Campaign_Users.AddDate < @ReportEndDate AND
Sales_Campaign_Status_Log.MRM_UserGUID IS NULL)

END
ELSE
BEGIN

INSERT INTO #Campaign_Status
SELECT
Sales_Campaign_Users.ItemID,
Sales_Campaign_Users.CampaignID,
IsNull(Sales_Campaign_Status_Log.StatusID, 0),
Sales_Campaign_Users.MemberGUID,
Sales_Campaign_Status_Log.MRM_UserGUID,
IsNull(Sales_Campaign_Status_Log.LogDate, dbo.Sales_Campaign_Users.AddDate),
IsNULL(Sales_Campaign_Status_Log.Active, 1),
Member.BranchID,
NULL
FROM
Sales_Campaign_Users (NOLOCK)
LEFT JOIN
Sales_Campaign_Status_Log (NOLOCK)
ON
Sales_Campaign_Status_Log.ItemID = Sales_Campaign_Users.ItemID AND
Sales_Campaign_Status_Log.Active = 1
LEFT JOIN
Member (NOLOCK)
ON
Member.FID = @FID AND
Member.MemberGUID = Sales_Campaign_Users.MemberGUID
WHERE
(Sales_Campaign_Users.CampaignID IN (SELECT value FROM dbo.fn_Split(@CampaignIDs, ',')) AND
Sales_Campaign_Status_Log.LogDate > @ReportStartDate AND
Sales_Campaign_Status_Log.LogDate < @ReportEndDate)
OR
(dbo.Sales_Campaign_Users.CampaignID IN (SELECT value FROM dbo.fn_Split(@CampaignIDs, ',')) AND
dbo.Sales_Campaign_Users.StatusID IN (SELECT value FROM dbo.fn_Split_INTEGER(@Status, ',')) AND
dbo.Sales_Campaign_Users.AddDate > @ReportStartDate AND
dbo.Sales_Campaign_Users.AddDate < @ReportEndDate AND
Sales_Campaign_Status_Log.MRM_UserGUID IS NULL)

END

UPDATE #Campaign_Status
SET #Campaign_Status.Teller_BranchID = #Teller_Branch.BranchID
FROM
#Teller_Branch (NOLOCK)
WHERE
#Teller_Branch.UserGUID = #Campaign_Status.MRMUserGUID AND
#Campaign_Status.LogDate >= #Teller_Branch.StartDate AND
#Campaign_Status.LogDate <= #Teller_Branch.EndDate

IF @UserStates IS NOT NULL
BEGIN

DELETE
FROM
#Campaign_Status
WHERE
#Campaign_Status.MemberGUID IN (
SELECT
#Campaign_Status.MemberGUID
FROM
#Campaign_Status
INNER JOIN
MEMBER (NOLOCK)
ON
MEMBER.MemberGUID = #Campaign_Status.MemberGUID
WHERE
MEMBER.PrimaryState NOT IN (SELECT value FROM dbo.fn_Split(@UserStates, ',')))

END

IF @UserZIPs IS NOT NULL
BEGIN

DELETE
FROM
#Campaign_Status
WHERE
#Campaign_Status.MemberGUID IN (
SELECT
#Campaign_Status.MemberGUID
FROM
#Campaign_Status
INNER JOIN
MEMBER (NOLOCK)
ON
MEMBER.MemberGUID = #Campaign_Status.MemberGUID
WHERE
CONVERT(INTEGER, REPLACE(CONVERT(NVARCHAR(5), MEMBER.PrimaryZIP), ' ', 0)) NOT IN (SELECT value FROM dbo.fn_Split_INTEGER(@UserZIPs, ',')))

END

IF @AgentGUIDs IS NOT NULL
BEGIN
DELETE
FROM
#Campaign_Status
WHERE
#Campaign_Status.MRMUserGUID NOT IN (SELECT value FROM dbo.fn_Split_UNIQUEIDENTIFIER(@AgentGUIDs, ','))
END

IF @Status IS NOT NULL AND @Status <> @DefaultStatusSearch
BEGIN
DELETE
FROM
#Campaign_Status
WHERE
#Campaign_Status.StatusID NOT IN (SELECT value FROM dbo.fn_Split_INTEGER(@Status, ','))
END

IF @Departments IS NOT NULL
BEGIN
DELETE
FROM
#Campaign_Status
WHERE
#Campaign_Status.MRMUserGUID NOT IN (
SELECT
UserGUID
FROM
MRM_User (NOLOCK)
WHERE
MRM_User.Default_DepartmentID IN (SELECT value FROM dbo.fn_Split_INTEGER(@Departments, ',')))
END

IF @TellerBranches IS NOT NULL
BEGIN

DELETE
FROM
#Campaign_Status
WHERE
#Campaign_Status.ItemID NOT IN (
SELECT
#Campaign_Status.ItemID
FROM
#Campaign_Status (NOLOCK)
INNER JOIN
#Teller_Branch (NOLOCK)
ON
#Teller_Branch.UserGUID = #Campaign_Status.MRMUserGUID
WHERE
#Campaign_Status.LogDate >= #Teller_Branch.StartDate AND
#Campaign_Status.LogDate <= #Teller_Branch.EndDate AND
#Teller_Branch.BranchID IN (SELECT value FROM dbo.fn_Split_INTEGER(@TellerBranches, ',')))

END

IF @MemberBranches IS NOT NULL
BEGIN

DELETE
FROM
#Campaign_Status
WHERE
#Campaign_Status.Member_BranchID NOT IN (SELECT value FROM dbo.fn_Split_INTEGER(@MemberBranches, ','))

END

IF @ShowInactive = 0
BEGIN

DELETE
FROM
#Campaign_Status
WHERE
ACTIVE = 0

END

SELECT
StatusID,
Name
FROM
Sales_Campaign_Status (NOLOCK)
WHERE
FID = @FID AND
StatusID IN (SELECT value FROM dbo.fn_Split_INTEGER(@Status, ','))
ORDER BY
StatusID

SELECT
Sales_Campaign.CampaignID,
Sales_Campaign.BeginDate,
Sales_Campaign.EndDate,
Sales_Campaign.Name,
Sales_Campaign.StatusID,
Sales_Campaign.CampaignType,
@ReportStartDate AS 'Report_Start_Date',
@ReportEndDate AS 'Report_End_Date'
FROM
Sales_Campaign (NOLOCK)
WHERE
Sales_Campaign.FID = @FID AND
Sales_Campaign.CampaignID IN (SELECT value FROM dbo.fn_Split(@CampaignIDs, ','))

SELECT
#Campaign_Status.CampaignID,
#Campaign_Status.StatusID,
dbo.Sales_Campaign_Status.Name,
COUNT(#Campaign_Status.StatusID) AS 'Total'
FROM
#Campaign_Status (NOLOCK)
INNER JOIN
dbo.Sales_Campaign_Status (NOLOCK)
ON
Sales_Campaign_Status.StatusID = #Campaign_Status.StatusID AND
Sales_Campaign_Status.FID = @FID
GROUP BY
#Campaign_Status.CampaignID, #Campaign_Status.StatusID, dbo.Sales_Campaign_Status.Name
ORDER BY
#Campaign_Status.CampaignID, #Campaign_Status.StatusID

UPDATE #Campaign_Status
SET #Campaign_Status.Teller_BranchID = 0
WHERE
#Campaign_Status.Teller_BranchID IS NULL

UPDATE #Campaign_Status
SET #Campaign_Status.Member_BranchID = 0
WHERE
#Campaign_Status.Member_BranchID IS NULL

SELECT
#Campaign_Status.ItemID,
#Campaign_Status.LogDate,
#Campaign_Status.Active,
#Campaign_Status.StatusID,
#Campaign_Status.CampaignID,
Member.MemberNumber,
Member.FirstName,
Member.LastName,
Member.PrimaryState,
Member.PrimaryZip,
#Campaign_Status.Member_BranchID,
Branch_Member.BranchName AS Member_BranchName,
MRM_User.FirstName AS MRM_User_FirstName,
MRM_User.LastName AS MRM_User_LastName,
MRM_User.Default_DepartmentID,
#Campaign_Status.Teller_BranchID,
Branch_Teller.BranchName AS Teller_BranchName,
FIDepartments.Name AS DepartmentName,
Sales_Campaign_Status.Name AS StatusName
FROM
#Campaign_Status (NOLOCK)
INNER JOIN
Member (NOLOCK)
ON
#Campaign_Status.MemberGUID = Member.MemberGUID
INNER JOIN
Sales_Campaign_Status (NOLOCK)
ON
Sales_Campaign_Status.FID = @FID AND
Sales_Campaign_Status.StatusID = #Campaign_Status.StatusID
LEFT JOIN
MRM_User (NOLOCK)
ON
MRM_User.FID = @FID AND
MRM_User.UserGUID = #Campaign_Status.MRMUserGUID
LEFT JOIN
FIDepartments (NOLOCK)
ON
FIDepartments.FID = @FID AND
FIDepartments.DepartmentID = MRM_User.Default_DepartmentID
LEFT JOIN
Branch Branch_Teller (NOLOCK)
ON
Branch_Teller.FID = @FID AND
Branch_Teller.BranchID = #Campaign_Status.Teller_BranchID
LEFT JOIN
Branch Branch_Member (NOLOCK)
ON
Branch_Member.FID = @FID AND
Branch_Member.BranchID = #Campaign_Status.Member_BranchID

DROP TABLE #Teller_Branch

DROP TABLE #Campaign_Status

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-09-02 : 12:11:11

INSERT INTO
#Teller_Branch
SELECT
User1.UserGUID,
User1.BranchID,
User1.StartDate,
(
SELECT
MIN(User2.StartDate)
FROM
MRM_User_Branch User2 (NOLOCK)
WHERE
User1.UserGUID = User2.UserGUID AND
User2.StartDate > User1.StartDate
GROUP BY
User1.UserGUID
)
FROM
MRM_User_Branch User1 (NOLOCK)
WHERE
User1.FID = @FID AND
User1.StartDate < @ReportStartDate

Jim

wabaker95
Starting Member

12 Posts

Posted - 2008-09-02 : 12:59:16

Hi jim,

I took out the group by expression. The stored procedure compiles without any errors. However, the web page that uses the stored procedure gives me the same error message from earlier.

Each GROUP BY expression must contain at least one column that is not an outer reference.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-09-02 : 14:52:38

looks like you are an adherent to the School Of NOLOCK. are you aware it can lead to incorrect results?

elsasoft.org

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-09-02 : 14:55:41

there's some discussion of it here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=109456

elsasoft.org

wabaker95
Starting Member

12 Posts

Posted - 2008-09-03 : 09:16:10

quote:
Originally posted by jezemine

looks like you are an adherent to the School Of NOLOCK. are you aware it can lead to incorrect results?

elsasoft.org

Actually I didn't write the stored procedure. It is just my responsibility to debug it. Good article about NOLOCK though.

wabaker95
Starting Member

12 Posts

Posted - 2008-09-03 : 09:58:16

quote:
Originally posted by jimf

INSERT INTO
#Teller_Branch
SELECT
User1.UserGUID,
User1.BranchID,
User1.StartDate,
(
SELECT
MIN(User2.StartDate)
FROM
MRM_User_Branch User2 (NOLOCK)
WHERE
User1.UserGUID = User2.UserGUID AND
User2.StartDate > User1.StartDate
GROUP BY
User1.UserGUID
)
FROM
MRM_User_Branch User1 (NOLOCK)
WHERE
User1.FID = @FID AND
User1.StartDate < @ReportStartDate

Jim

I commented out the group by expression again but this time on the production site instead of the development site and it worked. For some reason the development site is still not working but the production site is and that's what counts.

Thank you Jim.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-09-03 : 10:07:40

this app clearly for a bank or some other financial app. In such a case it's very bad to be using NOLOCK. That is unless you don't care to get account balances correct. I'm sure your customers care about that though...

you should at least file a bug if you aren't going to fix the NOLOCK problems now.

also tell us what bank it is so we can remove our money from there.

elsasoft.org

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources