RE: In store procedure where i need to declare "Declare @SQL1 varchar(800)" to access within Begin statement

arshad236

SSC Enthusiast

Points: 129

December 21, 2007 at 1:55 pm

#762327

hmmm.... I need only output query out of it to execute the query and debug accordingly.

ALTER PROCEDURE test]

(

@GroupID int,

@IsPortfolioManager bit = 1,

@Username varchar(20) = NULL,

@Debug bit=0

)

-- Get the GroupType for this Group

DECLARE @GroupType smallint

DECLARE @SQL1 varchar(8000)

DECLARE @SQL2 varchar(8000)

DECLARE @SQL3 varchar(8000)

DECLARE @SQL4 varchar(8000)

DECLARE @SQL5 varchar(8000)

DECLARE @SQL6 varchar(8000)

SET @SQL1 = ''

SET @SQL2 = ''

SET @SQL3 = ''

SET @SQL4 = ''

SET @SQL5 = ''

SET @SQL6 = ''

SET @GroupType = ( SELECT GroupType FROM PortfolioManager.dbo.GroupInfo WHERE GroupID = @GroupID )

IF @Username IS NOT NULL

BEGIN

DECLARE @FirmID smallint

SET @FirmID = ( SELECT FirmParameterID FROM ASAT.dbo.UserInformation WHERE Username = @Username )

-- Create a temp table containing the specified user's rep list

DECLARE @reps

TABLE (RepID char(4) PRIMARY KEY)

-- Populate the temp rep table

INSERT INTO @reps

SELECT DISTINCT

rl.RepID

FROM

ASAT..RepList rl WITH (NOLOCK)

LEFT JOIN Rep..BS_REP_INFO ri WITH (NOLOCK) ON rl.RepID = ri.REP_ID

WHERE

rl.username = @Username AND

rl.replisttype = 'BP'

IF @GroupType IN (1,2,4)

BEGIN

-- 1 Deep (All group types)

set @SQL1='SELECT

acc.AccountId,

acc.AccountLocationCode,

loc.AccountLocationShortName,

acc.RepId,

acc.AccountNo,

acc.SponsorAccountNo,

(CASE WHEN acc.AccountLocationCode IN (1, 2) THEN acc.AccountNo ELSE acc.SponsorAccountNo END) AS AccountNo,

(CASE WHEN acc.AccountLocationCode IN (1, 2) THEN SUBSTRING(acc.AccountNo, 1, 4) + ''-'' + SUBSTRING(acc.AccountNo, 5, 4) ELSE acc.SponsorAccountNo END) AS AccountNoDisplay,

(CASE WHEN acc.AccountLocationCode IN (1, 2) THEN acls.ShortName ELSE mfvs.SponsorName END) AS AccountClassOrFamily,

(CASE WHEN acc.AccountLocationCode IN (1, 2) THEN acc.AccountClassCode ELSE acc.SponsorCode END) AS AccountClassOrFamilyCode,

acc.SponsorCode,

acc.Cusip,

acc.ProductName,

acc.SSNTaxId,

acc.IsSSN,

acc.PrimaryBirthDate,

acc.AccountName,

acc.AccountTitle,

acc.IsClosed,

acc.TotalAccountValue,

acc.AccountValue,

acc.OutsideAccountValue,

(CASE WHEN acc.AccountLocationCode IN (1,2) THEN acc.AccountValue WHEN acc.AccountLocationCode = 16 THEN acc.OutsideAccountValue ELSE acc.TotalAccountValue END) AS AccountValue,

(CASE WHEN acc.AccountLocationCode IN (1,2) THEN acc.OutsideAccountValue ELSE 0 END) AS NetworkedValue,

acc.NetworkedAccountID,

(CASE WHEN acls.MFTradingEligible = ''Y'' AND RTRIM(acc.AccountClassCode) NOT LIKE ''%IS'' THEN ''Y'' ELSE ''N'' END) AS MFTradingEligible,

acls.EquityTradingEligible,

acls.OptionTradingEligible,

acls.FixedIncomeTradingEligible,

''Account'' AS MemberType

FROM

PortfolioManager.dbo.AccountMember am

INNER JOIN Customer.dbo.Account acc ON am.AccountId = acc.AccountId AND am.AccountLocationCode = acc.AccountLocationCode

INNER JOIN @reps reps ON acc.RepID = reps.RepID

INNER JOIN Customer.dbo.AccountLocation loc ON am.AccountLocationCode = loc.AccountLocationCode

LEFT JOIN Support.dbo.AccountClass acls ON acc.AccountClassCode = acls.Code

LEFT JOIN Customer.dbo.MFVASponsor mfvs ON acc.SponsorCode = mfvs.SponsorCode AND acc.SponsorType = mfvs.SponsorType

WHERE

am.GroupID = @GroupID

AND (@IsPortfolioManager = 1 OR acc.AccountLocationCode <> 16)

AND (@FirmID = 1 OR acc.AccountLocationCode IN (1,2))

AND (acc.AccountLocationCode NOT IN (4, 128, 2048) OR acc.suppress IS NULL OR acc.suppress = 0)'

END

-- If records were found, return immediately

IF @@ROWCOUNT > 0 RETURN 1

IF @FirmID = 1

BEGIN

-- 2 Deep (Household only - User Defined groups are handled below)

IF @GroupType = 2

BEGIN

set @SQL2=

'SELECT

acc.AccountId,

acc.AccountLocationCode,

loc.AccountLocationShortName,

acc.RepId,

acc.AccountNo,

acc.SponsorAccountNo,

(CASE WHEN acc.AccountLocationCode IN (1, 2) THEN acc.AccountNo ELSE acc.SponsorAccountNo END) AS AccountNo,

(CASE WHEN acc.AccountLocationCode IN (1, 2) THEN SUBSTRING(acc.AccountNo, 1, 4) + ''-'' + SUBSTRING(acc.AccountNo, 5, 4) ELSE acc.SponsorAccountNo END) AS AccountNoDisplay,

(CASE WHEN acc.AccountLocationCode IN (1, 2) THEN acls.ShortName ELSE mfvs.SponsorName END) AS AccountClassOrFamily,

(CASE WHEN acc.AccountLocationCode IN (1, 2) THEN acc.AccountClassCode ELSE acc.SponsorCode END) AS AccountClassOrFamilyCode,

acc.SponsorCode,

acc.Cusip,

acc.ProductName,

acc.SSNTaxId,

acc.IsSSN,

acc.PrimaryBirthDate,

acc.AccountName,

acc.AccountTitle,

acc.IsClosed,

acc.TotalAccountValue,

acc.AccountValue,

acc.OutsideAccountValue,

(CASE WHEN acc.AccountLocationCode IN (1,2) THEN acc.AccountValue WHEN acc.AccountLocationCode = 16 THEN acc.OutsideAccountValue ELSE acc.TotalAccountValue END) AS AccountValue,

(CASE WHEN acc.AccountLocationCode IN (1,2) THEN acc.OutsideAccountValue ELSE 0 END) AS NetworkedValue,

acc.NetworkedAccountID,

(CASE WHEN acls.MFTradingEligible = ''Y'' AND RTRIM(acc.AccountClassCode) NOT LIKE ''%IS'' THEN ''Y'' ELSE ''N'' END) AS MFTradingEligible,

acls.EquityTradingEligible,

acls.OptionTradingEligible,

acls.FixedIncomeTradingEligible,

gt.Description AS MemberType

FROM

PortfolioManager.dbo.GroupMember gm

INNER JOIN PortfolioManager.dbo.GroupInfo gi ON gm.MemberGroupID = gi.GroupID

INNER JOIN PortfolioManager.dbo.GroupType gt ON gi.GroupType = gt.GroupTypeID

INNER JOIN PortfolioManager.dbo.AccountMember am ON gm.MemberGroupID = am.GroupID

INNER JOIN Customer.dbo.Account acc ON am.AccountId = acc.AccountId AND am.AccountLocationCode = acc.AccountLocationCode

INNER JOIN @reps reps ON acc.RepID = reps.RepID

INNER JOIN Customer.dbo.AccountLocation loc ON am.AccountLocationCode = loc.AccountLocationCode

LEFT JOIN Support.dbo.AccountClass acls ON acc.AccountClassCode = acls.Code

LEFT JOIN Customer.dbo.MFVASponsor mfvs ON acc.SponsorCode = mfvs.SponsorCode AND acc.SponsorType = mfvs.SponsorType

WHERE

gm.GroupID = @GroupID

AND (@IsPortfolioManager = 1 OR acc.AccountLocationCode <> 16)

AND (@FirmID = 1 OR acc.AccountLocationCode IN (1,2))

AND (acc.AccountLocationCode NOT IN (4, 128, 2048) OR acc.suppress IS NULL OR acc.suppress = 0)

-- Ensure that any interim child groups actually exist in GroupInfo (unknown why they would be orphaned, but it happens apparently)

AND EXISTS (SELECT * FROM PortfolioManager.dbo.GroupInfo gi WHERE gi.GroupID = gm.MemberGroupID)'

END

-- If records were found, return immediately

IF @@ROWCOUNT > 0 RETURN 2

-- 2 or 3 Deep (User Defined groups only)

IF @GroupType = 4

BEGIN

set @SQL3='SELECT

acc.AccountId,

acc.AccountLocationCode,

loc.AccountLocationShortName,

acc.RepId,

acc.AccountNo,

acc.SponsorAccountNo,

(CASE WHEN acc.AccountLocationCode IN (1, 2) THEN acc.AccountNo ELSE acc.SponsorAccountNo END) AS AccountNo,

(CASE WHEN acc.AccountLocationCode IN (1, 2) THEN SUBSTRING(acc.AccountNo, 1, 4) + ''-'' + SUBSTRING(acc.AccountNo, 5, 4) ELSE acc.SponsorAccountNo END) AS AccountNoDisplay,

(CASE WHEN acc.AccountLocationCode IN (1, 2) THEN acls.ShortName ELSE mfvs.SponsorName END) AS AccountClassOrFamily,

(CASE WHEN acc.AccountLocationCode IN (1, 2) THEN acc.AccountClassCode ELSE acc.SponsorCode END) AS AccountClassOrFamilyCode,

acc.SponsorCode,

acc.Cusip,

acc.ProductName,

acc.SSNTaxId,

acc.IsSSN,

acc.PrimaryBirthDate,

acc.AccountName,

acc.AccountTitle,

acc.IsClosed,

acc.TotalAccountValue,

acc.AccountValue,

acc.OutsideAccountValue,

(CASE WHEN acc.AccountLocationCode IN (1,2) THEN acc.AccountValue WHEN acc.AccountLocationCode = 16 THEN acc.OutsideAccountValue ELSE acc.TotalAccountValue END) AS AccountValue,

(CASE WHEN acc.AccountLocationCode IN (1,2) THEN acc.OutsideAccountValue ELSE 0 END) AS NetworkedValue,

acc.NetworkedAccountID,

(CASE WHEN acls.MFTradingEligible = ''Y'' AND RTRIM(acc.AccountClassCode) NOT LIKE ''%IS'' THEN ''Y'' ELSE ''N'' END) AS MFTradingEligible,

acls.EquityTradingEligible,

acls.OptionTradingEligible,

acls.FixedIncomeTradingEligible,

gt.Description AS MemberType

FROM

PortfolioManager.dbo.GroupMember gm

INNER JOIN PortfolioManager.dbo.GroupInfo gi ON gm.MemberGroupID = gi.GroupID

INNER JOIN PortfolioManager.dbo.GroupType gt ON gi.GroupType = gt.GroupTypeID

INNER JOIN PortfolioManager.dbo.AccountMember am ON gm.MemberGroupID = am.GroupID

INNER JOIN Customer.dbo.Account acc ON am.AccountId = acc.AccountId AND am.AccountLocationCode = acc.AccountLocationCode

INNER JOIN @reps reps ON acc.RepID = reps.RepID

INNER JOIN Customer.dbo.AccountLocation loc ON am.AccountLocationCode = loc.AccountLocationCode

LEFT JOIN Support.dbo.AccountClass acls ON acc.AccountClassCode = acls.Code

LEFT JOIN Customer.dbo.MFVASponsor mfvs ON acc.SponsorCode = mfvs.SponsorCode AND acc.SponsorType = mfvs.SponsorType

WHERE

gm.GroupID = @GroupID

AND (@IsPortfolioManager = 1 OR acc.AccountLocationCode <> 16)

AND (@FirmID = 1 OR acc.AccountLocationCode IN (1,2))

AND (acc.AccountLocationCode NOT IN (4, 128, 2048) OR acc.suppress IS NULL OR acc.suppress = 0)

-- Ensure that any interim child groups actually exist in GroupInfo (unknown why they would be orphaned, but it happens apparently)

AND EXISTS (SELECT * FROM PortfolioManager.dbo.GroupInfo gi WHERE gi.GroupID = gm.MemberGroupID)

UNION ALL

SELECT

acc.AccountId,

acc.AccountLocationCode,

loc.AccountLocationShortName,

acc.RepId,

acc.AccountNo,

acc.SponsorAccountNo,

(CASE WHEN acc.AccountLocationCode IN (1, 2) THEN acc.AccountNo ELSE acc.SponsorAccountNo END) AS AccountNo,

(CASE WHEN acc.AccountLocationCode IN (1, 2) THEN SUBSTRING(acc.AccountNo, 1, 4) + ''-'' + SUBSTRING(acc.AccountNo, 5, 4) ELSE acc.SponsorAccountNo END) AS AccountNoDisplay,

(CASE WHEN acc.AccountLocationCode IN (1, 2) THEN acls.ShortName ELSE mfvs.SponsorName END) AS AccountClassOrFamily,

(CASE WHEN acc.AccountLocationCode IN (1, 2) THEN acc.AccountClassCode ELSE acc.SponsorCode END) AS AccountClassOrFamilyCode,

acc.SponsorCode,

acc.Cusip,

acc.ProductName,

acc.SSNTaxId,

acc.IsSSN,

acc.PrimaryBirthDate,

acc.AccountName,

acc.AccountTitle,

acc.IsClosed,

acc.TotalAccountValue,

acc.AccountValue,

acc.OutsideAccountValue,

(CASE WHEN acc.AccountLocationCode IN (1,2) THEN acc.AccountValue WHEN acc.AccountLocationCode = 16 THEN acc.OutsideAccountValue ELSE acc.TotalAccountValue END) AS AccountValue,

(CASE WHEN acc.AccountLocationCode IN (1,2) THEN acc.OutsideAccountValue ELSE 0 END) AS NetworkedValue,

acc.NetworkedAccountID,

(CASE WHEN acls.MFTradingEligible = ''Y'' AND RTRIM(acc.AccountClassCode) NOT LIKE ''%IS'' THEN ''Y'' ELSE ''N'' END) AS MFTradingEligible,

acls.EquityTradingEligible,

acls.OptionTradingEligible,

acls.FixedIncomeTradingEligible,

''Household'' AS MemberType

FROM

PortfolioManager.dbo.GroupMember ggm

INNER JOIN PortfolioManager.dbo.GroupMember gm ON ggm.MemberGroupID = gm.GroupID

INNER JOIN PortfolioManager.dbo.AccountMember am ON gm.MemberGroupID = am.GroupID

INNER JOIN Customer.dbo.Account acc ON am.AccountId = acc.AccountId AND am.AccountLocationCode = acc.AccountLocationCode

INNER JOIN @reps reps ON acc.RepID = reps.RepID

INNER JOIN Customer.dbo.AccountLocation loc ON am.AccountLocationCode = loc.AccountLocationCode

LEFT JOIN Support.dbo.AccountClass acls ON acc.AccountClassCode = acls.Code

LEFT JOIN Customer.dbo.MFVASponsor mfvs ON acc.SponsorCode = mfvs.SponsorCode AND acc.SponsorType = mfvs.SponsorType

WHERE

ggm.GroupID = @GroupID

AND (@IsPortfolioManager = 1 OR acc.AccountLocationCode <> 16)

AND (@FirmID = 1 OR acc.AccountLocationCode IN (1,2))

AND (acc.AccountLocationCode NOT IN (4, 128, 2048) OR acc.suppress IS NULL OR acc.suppress = 0)

-- Ensure that any interim child groups actually exist in GroupInfo (unknown why they would be orphaned, but it happens apparently)

AND EXISTS (SELECT * FROM PortfolioManager.dbo.GroupInfo gi WHERE gi.GroupID = gm.MemberGroupID)

AND EXISTS (SELECT * FROM PortfolioManager.dbo.GroupInfo gi WHERE gi.GroupID = ggm.MemberGroupID)'

END

-- If records were found, return immediately

IF @@ROWCOUNT > 0 RETURN 3

END

-- No records were found - group is empty

RETURN 0

END

ELSE

BEGIN

-- THE FOLLOWING QUERIES ARE FOR EMPLOYEES ONLY --

-- These have no RepID join (and therefore no access restriction

-- These queries must remain identical to those above, but without the JOIN to a list of reps.

IF @GroupType IN (1,2,4)

BEGIN

-- 1 Deep

set @SQL4='SELECT

acc.AccountId,

acc.AccountLocationCode,

loc.AccountLocationShortName,

acc.RepId,

acc.AccountNo,

acc.SponsorAccountNo,

(CASE WHEN acc.AccountLocationCode IN (1, 2) THEN acc.AccountNo ELSE acc.SponsorAccountNo END) AS AccountNo,

(CASE WHEN acc.AccountLocationCode IN (1, 2) THEN SUBSTRING(acc.AccountNo, 1, 4) + ''-'' + SUBSTRING(acc.AccountNo, 5, 4) ELSE acc.SponsorAccountNo END) AS AccountNoDisplay,

(CASE WHEN acc.AccountLocationCode IN (1, 2) THEN acls.ShortName ELSE mfvs.SponsorName END) AS AccountClassOrFamily,

(CASE WHEN acc.AccountLocationCode IN (1, 2) THEN acc.AccountClassCode ELSE acc.SponsorCode END) AS AccountClassOrFamilyCode,

acc.SponsorCode,

acc.Cusip,

acc.ProductName,

acc.SSNTaxId,

acc.IsSSN,

acc.PrimaryBirthDate,

acc.AccountName,

acc.AccountTitle,

acc.IsClosed,

acc.TotalAccountValue,

acc.AccountValue,

acc.OutsideAccountValue,

(CASE WHEN acc.AccountLocationCode IN (1,2) THEN acc.AccountValue WHEN acc.AccountLocationCode = 16 THEN acc.OutsideAccountValue ELSE acc.TotalAccountValue END) AS AccountValue,

(CASE WHEN acc.AccountLocationCode IN (1,2) THEN acc.OutsideAccountValue ELSE 0 END) AS NetworkedValue,

acc.NetworkedAccountID,

(CASE WHEN acls.MFTradingEligible = ''Y'' AND RTRIM(acc.AccountClassCode) NOT LIKE ''%IS'' THEN ''Y'' ELSE ''N'' END) AS MFTradingEligible,

acls.EquityTradingEligible,

acls.OptionTradingEligible,

acls.FixedIncomeTradingEligible,

''Account'' AS MemberType

FROM

PortfolioManager.dbo.AccountMember am

INNER JOIN Customer.dbo.Account acc ON am.AccountId = acc.AccountId AND am.AccountLocationCode = acc.AccountLocationCode

INNER JOIN Customer.dbo.AccountLocation loc ON am.AccountLocationCode = loc.AccountLocationCode

LEFT JOIN Support.dbo.AccountClass acls ON acc.AccountClassCode = acls.Code

LEFT JOIN Customer.dbo.MFVASponsor mfvs ON acc.SponsorCode = mfvs.SponsorCode AND acc.SponsorType = mfvs.SponsorType

WHERE

am.GroupID = @GroupID

AND acc.RepId <> ''????''

AND (@IsPortfolioManager = 1 OR acc.AccountLocationCode <> 16)

AND (acc.AccountLocationCode NOT IN (4, 128, 2048) OR acc.suppress IS NULL OR acc.suppress = 0)'

END

-- If records were found, return immediately

IF @@ROWCOUNT > 0 RETURN 1

-- 2 Deep (Household only - User Defined groups are handled below)

IF @GroupType = 2

BEGIN

set @SQL5='SELECT

acc.AccountId,

acc.AccountLocationCode,

loc.AccountLocationShortName,

acc.RepId,

acc.AccountNo,

acc.SponsorAccountNo,

(CASE WHEN acc.AccountLocationCode IN (1, 2) THEN acc.AccountNo ELSE acc.SponsorAccountNo END) AS AccountNo,

(CASE WHEN acc.AccountLocationCode IN (1, 2) THEN SUBSTRING(acc.AccountNo, 1, 4) + ''-'' + SUBSTRING(acc.AccountNo, 5, 4) ELSE acc.SponsorAccountNo END) AS AccountNoDisplay,

(CASE WHEN acc.AccountLocationCode IN (1, 2) THEN acls.ShortName ELSE mfvs.SponsorName END) AS AccountClassOrFamily,

(CASE WHEN acc.AccountLocationCode IN (1, 2) THEN acc.AccountClassCode ELSE acc.SponsorCode END) AS AccountClassOrFamilyCode,

acc.SponsorCode,

acc.Cusip,

acc.ProductName,

acc.SSNTaxId,

acc.IsSSN,

acc.PrimaryBirthDate,

acc.AccountName,

acc.AccountTitle,

acc.IsClosed,

acc.TotalAccountValue,

acc.AccountValue,

acc.OutsideAccountValue,

(CASE WHEN acc.AccountLocationCode IN (1,2) THEN acc.AccountValue WHEN acc.AccountLocationCode = 16 THEN acc.OutsideAccountValue ELSE acc.TotalAccountValue END) AS AccountValue,

(CASE WHEN acc.AccountLocationCode IN (1,2) THEN acc.OutsideAccountValue ELSE 0 END) AS NetworkedValue,

acc.NetworkedAccountID,

(CASE WHEN acls.MFTradingEligible = ''Y'' AND RTRIM(acc.AccountClassCode) NOT LIKE ''%IS'' THEN ''Y'' ELSE ''N'' END) AS MFTradingEligible,

acls.EquityTradingEligible,

acls.OptionTradingEligible,

acls.FixedIncomeTradingEligible,

gt.Description AS MemberType

FROM

PortfolioManager.dbo.GroupMember gm

INNER JOIN PortfolioManager.dbo.GroupInfo gi ON gm.MemberGroupID = gi.GroupID

INNER JOIN PortfolioManager.dbo.GroupType gt ON gi.GroupType = gt.GroupTypeID

INNER JOIN PortfolioManager.dbo.AccountMember am ON gm.MemberGroupID = am.GroupID

INNER JOIN Customer.dbo.Account acc ON am.AccountId = acc.AccountId AND am.AccountLocationCode = acc.AccountLocationCode

INNER JOIN Customer.dbo.AccountLocation loc ON am.AccountLocationCode = loc.AccountLocationCode

LEFT JOIN Support.dbo.AccountClass acls ON acc.AccountClassCode = acls.Code

LEFT JOIN Customer.dbo.MFVASponsor mfvs ON acc.SponsorCode = mfvs.SponsorCode AND acc.SponsorType = mfvs.SponsorType

WHERE

gm.GroupID = @GroupID

AND acc.RepId <> ''????''

AND (@IsPortfolioManager = 1 OR acc.AccountLocationCode <> 16)

AND (acc.AccountLocationCode NOT IN (4, 128, 2048) OR acc.suppress IS NULL OR acc.suppress = 0)

-- Ensure that any interim child groups actually exist in GroupInfo (unknown why they would be orphaned, but it happens apparently)

AND EXISTS (SELECT * FROM PortfolioManager.dbo.GroupInfo gi WHERE gi.GroupID = gm.MemberGroupID)'

END

-- If records were found, return immediately

IF @@ROWCOUNT > 0 RETURN 2

-- 2 or 3 Deep (User Defined groups only)

IF @GroupType = 4

BEGIN

set @SQL6='SELECT

acc.AccountId,

acc.AccountLocationCode,

loc.AccountLocationShortName,

acc.RepId,

acc.AccountNo,

acc.SponsorAccountNo,

(CASE WHEN acc.AccountLocationCode IN (1, 2) THEN acc.AccountNo ELSE acc.SponsorAccountNo END) AS AccountNo,

(CASE WHEN acc.AccountLocationCode IN (1, 2) THEN SUBSTRING(acc.AccountNo, 1, 4) + ''-'' + SUBSTRING(acc.AccountNo, 5, 4) ELSE acc.SponsorAccountNo END) AS AccountNoDisplay,

(CASE WHEN acc.AccountLocationCode IN (1, 2) THEN acls.ShortName ELSE mfvs.SponsorName END) AS AccountClassOrFamily,

(CASE WHEN acc.AccountLocationCode IN (1, 2) THEN acc.AccountClassCode ELSE acc.SponsorCode END) AS AccountClassOrFamilyCode,

acc.SponsorCode,

acc.Cusip,

acc.ProductName,

acc.SSNTaxId,

acc.IsSSN,

acc.PrimaryBirthDate,

acc.AccountName,

acc.AccountTitle,

acc.IsClosed,

acc.TotalAccountValue,

acc.AccountValue,

acc.OutsideAccountValue,

(CASE WHEN acc.AccountLocationCode IN (1,2) THEN acc.AccountValue WHEN acc.AccountLocationCode = 16 THEN acc.OutsideAccountValue ELSE acc.TotalAccountValue END) AS AccountValue,

(CASE WHEN acc.AccountLocationCode IN (1,2) THEN acc.OutsideAccountValue ELSE 0 END) AS NetworkedValue,

acc.NetworkedAccountID,

(CASE WHEN acls.MFTradingEligible = ''Y'' AND RTRIM(acc.AccountClassCode) NOT LIKE ''%IS'' THEN ''Y'' ELSE ''N'' END) AS MFTradingEligible,

acls.EquityTradingEligible,

acls.OptionTradingEligible,

acls.FixedIncomeTradingEligible,

gt.Description AS MemberType

FROM

PortfolioManager.dbo.GroupMember gm

INNER JOIN PortfolioManager.dbo.GroupInfo gi ON gm.MemberGroupID = gi.GroupID

INNER JOIN PortfolioManager.dbo.GroupType gt ON gi.GroupType = gt.GroupTypeID

INNER JOIN PortfolioManager.dbo.AccountMember am ON gm.MemberGroupID = am.GroupID

INNER JOIN Customer.dbo.Account acc ON am.AccountId = acc.AccountId AND am.AccountLocationCode = acc.AccountLocationCode

INNER JOIN Customer.dbo.AccountLocation loc ON am.AccountLocationCode = loc.AccountLocationCode

LEFT JOIN Support.dbo.AccountClass acls ON acc.AccountClassCode = acls.Code

LEFT JOIN Customer.dbo.MFVASponsor mfvs ON acc.SponsorCode = mfvs.SponsorCode AND acc.SponsorType = mfvs.SponsorType

WHERE

gm.GroupID = @GroupID

AND acc.RepId <> ''????''

AND (@IsPortfolioManager = 1 OR acc.AccountLocationCode <> 16)

AND (acc.AccountLocationCode NOT IN (4, 128, 2048) OR acc.suppress IS NULL OR acc.suppress = 0)

-- Ensure that any interim child groups actually exist in GroupInfo (unknown why they would be orphaned, but it happens apparently)

AND EXISTS (SELECT * FROM PortfolioManager.dbo.GroupInfo gi WHERE gi.GroupID = gm.MemberGroupID)

UNION ALL

SELECT

acc.AccountId,

acc.AccountLocationCode,

loc.AccountLocationShortName,

acc.RepId,

acc.AccountNo,

acc.SponsorAccountNo,

(CASE WHEN acc.AccountLocationCode IN (1, 2) THEN acc.AccountNo ELSE acc.SponsorAccountNo END) AS AccountNo,

(CASE WHEN acc.AccountLocationCode IN (1, 2) THEN SUBSTRING(acc.AccountNo, 1, 4) + '-' + SUBSTRING(acc.AccountNo, 5, 4) ELSE acc.SponsorAccountNo END) AS AccountNoDisplay,

(CASE WHEN acc.AccountLocationCode IN (1, 2) THEN acls.ShortName ELSE mfvs.SponsorName END) AS AccountClassOrFamily,

(CASE WHEN acc.AccountLocationCode IN (1, 2) THEN acc.AccountClassCode ELSE acc.SponsorCode END) AS AccountClassOrFamilyCode,

acc.SponsorCode,

acc.Cusip,

acc.ProductName,

acc.SSNTaxId,

acc.IsSSN,

acc.PrimaryBirthDate,

acc.AccountName,

acc.AccountTitle,

acc.IsClosed,

acc.TotalAccountValue,

acc.AccountValue,

acc.OutsideAccountValue,

(CASE WHEN acc.AccountLocationCode IN (1,2) THEN acc.AccountValue WHEN acc.AccountLocationCode = 16 THEN acc.OutsideAccountValue ELSE acc.TotalAccountValue END) AS AccountValue,

(CASE WHEN acc.AccountLocationCode IN (1,2) THEN acc.OutsideAccountValue ELSE 0 END) AS NetworkedValue,

acc.NetworkedAccountID,

(CASE WHEN acls.MFTradingEligible = ''Y'' AND RTRIM(acc.AccountClassCode) NOT LIKE ''%IS'' THEN ''Y'' ELSE ''N'' END) AS MFTradingEligible,

acls.EquityTradingEligible,

acls.OptionTradingEligible,

acls.FixedIncomeTradingEligible,

''Household'' AS MemberType

FROM

PortfolioManager.dbo.GroupMember ggm

INNER JOIN PortfolioManager.dbo.GroupMember gm ON ggm.MemberGroupID = gm.GroupID

INNER JOIN PortfolioManager.dbo.AccountMember am ON gm.MemberGroupID = am.GroupID

INNER JOIN Customer.dbo.Account acc ON am.AccountId = acc.AccountId AND am.AccountLocationCode = acc.AccountLocationCode

INNER JOIN Customer.dbo.AccountLocation loc ON am.AccountLocationCode = loc.AccountLocationCode

LEFT JOIN Support.dbo.AccountClass acls ON acc.AccountClassCode = acls.Code

LEFT JOIN Customer.dbo.MFVASponsor mfvs ON acc.SponsorCode = mfvs.SponsorCode AND acc.SponsorType = mfvs.SponsorType

WHERE

ggm.GroupID = @GroupID

AND acc.RepId <> ''????''

AND (@IsPortfolioManager = 1 OR acc.AccountLocationCode <> 16)

AND (acc.AccountLocationCode NOT IN (4, 128, 2048) OR acc.suppress IS NULL OR acc.suppress = 0)

-- Ensure that any interim child groups actually exist in GroupInfo (unknown why they would be orphaned, but it happens apparently)

AND EXISTS (SELECT * FROM PortfolioManager.dbo.GroupInfo gi WHERE gi.GroupID = gm.MemberGroupID)

AND EXISTS (SELECT * FROM PortfolioManager.dbo.GroupInfo gi WHERE gi.GroupID = ggm.MemberGroupID)'

-- If records were found, return immediately

IF @@ROWCOUNT > 0 RETURN 3

-- No records were found - group is empty

RETURN 0

END

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

EXEC (@SQL1+@SQL2+@SQL3+@SQL4+@SQL5+@SQL6)

IF @Debug = 1

BEGIN

PRINT ISNULL(@SQL1,'NO SQL!')

PRINT ISNULL(@SQL2,'NO SQL!')

PRINT ISNULL(@SQL3,'NO SQL!')

PRINT ISNULL(@SQL4,'NO SQL!')

PRINT ISNULL(@SQL5,'NO SQL!')

PRINT ISNULL(@SQL6,'NO SQL!')

END