Problem with complex stored procedure

Question

Post reply

Problem with complex stored procedure

Wayne Baker

Grasshopper

Points: 11
More actions
September 2, 2008 at 1:29 pm

#197113

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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 1

Wayne,

Do you have a question to go along with this?

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

Wayne Baker Grasshopper Points: 11 More actions · Answer 2

yes I apologize David. I am getting a this error message

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

I tried to delete the group by expression GROUP BY

User1.UserGUID)

when it is inserted into the temporary table Teller_Branch and the stored procedure now runs when I hit execute

however, on the web page it still gives me the same error.

vaseem-773819 Ten Centuries Points: 1015 More actions · Answer 3

Hi

Definitely there is no need of Group by statement since you need only one coulumn MIN(User2.StartDate) and in where clause you already given User1.UserGuid = User2.UserGuid.

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

Thanks

Vaseem

Wayne Baker Grasshopper Points: 11 More actions · Answer 4

thank you vaseem. I commented out the group by expression again but this time on the production site instead of the development site and it worked.