SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
arshad236
arshad236
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 4
I have a complex store procedure and I need print of output from store procedure that's why i am declaring couple of variables to assign queries to variables and then to take a print at end of store procedure. I just need to debug store procedure that's why i am doing this because i can't debug by using Visual Studio. can any body help me. I am not good enough with SQL Server.w00t
Roy Ernest
Roy Ernest
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4890 Visits: 6870
I am not sure what you want to see... Or what help you need. You say it is complex. So why not just copy the body of the Stored proc into the analyzer and set the values to the variables and then do all the debugging you want. You have more control over it. You could comment out parts and execute just some statements.
Just my 2 cents

-Roy
Jack Corbett
  Jack Corbett
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24448 Visits: 14905
Are you getting any errors when you run the stored procedure? or is the problem that you are not getting the results you expect or no results at all?

I normally debug more complex procedures by commenting out all but a small section of the procedure and then running it and if it works enable the next section.

If you post the procedure it will be easier to help.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
arshad236
arshad236
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 4
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
)
AS
-- 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
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
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
arshad236
arshad236
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 4
I posted SP. Can you please take a look.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)

Group: General Forum Members
Points: 118031 Visits: 45534
Take this lot out from just before the exec. The GO breaks the batch and afterwards, the SQL1... variables are not defined.


GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


GilaMonster
GilaMonster
SSC Guru
SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)

Group: General Forum Members
Points: 118031 Visits: 45534
Edit. I need to read better.

A better way to debug than mess with dynamic SQL, which in this case is going to cause errors that didn't exist before, is to take the queries and run them one by one in QA/Management studio.

Just declare the necessary variables, and run the queries alone.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search