InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 20 Jun 2013 05:53:17 GMT20http://www.sqlservercentral.com/Forums/Topic435806-338-1.aspxEdit. 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.Fri, 21 Dec 2007 14:32:35 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic435806-338-1.aspxTake this lot out from just before the exec. The GO breaks the batch and afterwards, the SQL1... variables are not defined.[code]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO[/code]Fri, 21 Dec 2007 14:22:08 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic435806-338-1.aspxI posted SP. Can you please take a look.Fri, 21 Dec 2007 14:01:14 GMTarshad236http://www.sqlservercentral.com/Forums/Topic435806-338-1.aspxhmmm.... 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 GroupDECLARE @GroupType smallintDECLARE @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 0ENDELSEBEGIN -- 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 ENDENDGOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOEXEC (@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!') ENDFri, 21 Dec 2007 13:55:15 GMTarshad236http://www.sqlservercentral.com/Forums/Topic435806-338-1.aspxAre 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.Fri, 21 Dec 2007 12:13:58 GMT Jack Corbetthttp://www.sqlservercentral.com/Forums/Topic435806-338-1.aspxI 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 centsFri, 21 Dec 2007 12:09:46 GMTRoy Ernesthttp://www.sqlservercentral.com/Forums/Topic435806-338-1.aspxI 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:Fri, 21 Dec 2007 11:54:48 GMTarshad236