How to display a declared variable's value after a stored procedure has been executed ?

Is there a way, perhaps using an output parameter, that I can see the concatenated SQL string that gets created by executing the following stored procedure ? If this is the case, could you show me the coding statements I would need to add to this stored procedure to see the value of the SQL declared variable (@SQL) after it has been assigned a value by the following stored procedure.  


CREATE PROCEDURE dbo.procFAllStates
@RptYear int,
@Prop varchar (3),
@Agg varchar (3),
@IRA varchar (3),
@StateF varchar(200)


AS
DECLARE @DateFI AS DATETIME
DECLARE @DateMF AS DATETIME
DECLARE @DateC AS DATETIME
DECLARE @DateE AS DATETIME
DECLARE @DateI AS DATETIME
DECLARE @SQL  varchar(8000)

SELECT @DateFI = CAST((@RptYear - tblStatesAll.BondsFS) AS VARCHAR) + '-06-30' from tblStatesAll
SELECT @DateMF = CAST((@RptYear - tblStatesAll.MutualFS) AS VARCHAR) + '-06-30' from tblStatesAll
SELECT @DateC = CAST((@RptYear - tblStatesAll.CashFS) AS VARCHAR) + '-06-30' from tblStatesAll
SELECT @DateE = CAST((@RptYear - tblStatesAll.StocksFS) AS VARCHAR) + '-06-30' from tblStatesAll
SELECT @DateI = CAST(((@RptYear - tblStatesAll.IRAFS)+71) AS VARCHAR) + '-12-31' from tblStatesAll

If exists(select * from dbo.sysobjects where name = 'tblFAllStates' and type = 'U')
Drop table tblFAllStates

Set @SQL =
      'SELECT Null As Title, C.FirstName AS [First Name], C.MiddleInitial AS MI, C.LastName AS [Last Name], C.SecondNameFirst AS [Second Name First], C.SecondNameMid AS [Second Name Mid],
       C.SecondNameLast AS [Second Name Last], Null AS Relation, C.Address1 AS [Address 1], C.Address2 AS [Address 2], C.Address3 AS [Address 3], C.Address4 AS [Address 4], C.City, C.State,
       C.Zip, C.SSN, Null AS [DDA Number], Null As [Check/Cert Number], C.DateLost AS [Date Lost], Null As [Date Opened], Right(C.OfficeNumber,3) + '' '' + C.CustomerNumber AS [Account Number],
       C.DateOfBirth, P.PropertyType AS [Property Type], Null As [Property Status], P.CUSIP, P.SecurityName AS [Security Name], Null AS [Sub-Issue], P.MarketValue AS [Market Value],
       P.ClosePrice AS [Market Price], P.Quantity AS [Shares], P.CashBalance AS [Dollar Amount], C.DateOfBirth AS [Date Of Birth], P.IraCode, P.PlanNumber
       INTO tblFAllStates
       FROM tblStatesAll
       AS S INNER JOIN (tblCustomers AS C INNER JOIN tblProducts AS P ON C.CustomerNumber = P.CustomerNumber)  ON S.StateFS = C.State WHERE S.FallCycle = 1 '

If @Prop = 'ALL'
   BEGIN
     SET @SQL = @SQL + '  AND ((C.DateLost <=  ''' + CONVERT(VARCHAR(10), @DateFI, 111) + '''  AND P.PropertyType=''FIXED INCOME'' AND LEN(P.IraCode) = 0) '
     SET @SQL = @SQL + '  OR    (C.DateLost <=  ''' + CONVERT(VARCHAR(10), @DateMF, 111) + ''' AND P.PropertyType=''MUTUAL FUND'' AND PlanNumber = ''NETWORKED'' AND LEN(P.IraCode) = 0) '
     SET @SQL = @SQL + '  OR    (C.DateLost <=  ''' + CONVERT(VARCHAR(10), @DateMF, 111) + '''  AND P.PropertyType=''MUTUAL FUND'' AND P.PlanNumber <> ''NETWORKED'' AND LEN(P.IraCode) = 0) '
     SET @SQL = @SQL + '  OR    (C.DateLost <=  ''' + CONVERT(VARCHAR(10), @DateMF, 111) + ''' AND P.PropertyType=''OTHER'' AND LEN(P.IraCode) = 0) '  
     SET @SQL = @SQL + '  OR    (C.DateLost <=  ''' + CONVERT(VARCHAR(10), @DateE, 111) + '''AND P.PropertyType=''EQUITY'' AND LEN(P.IraCode) = 0)) '
   END

If @Prop = 'B'
   BEGIN
     SET @SQL = @SQL + '  AND (C.DateLost <= ''' + CONVERT(VARCHAR(10), @DateFI, 111) + ''' AND P.PropertyType=''FIXED INCOME'' AND LEN(P.IraCode) = 0) AND P.PropertyType=''FIXED INCOME'' AND LEN(P.IraCode) = 0) '
   END

If @Prop = 'MFA'
   BEGIN
     SET @SQL = @SQL + ' AND (C.DateLost <= ''' + CONVERT(VARCHAR(10), @DateMF, 111) + ''' AND P.PropertyType=''MUTUAL FUND'' AND P.PlanNumber <> ''NETWORKED'' AND LEN(P.IraCode) = 0) '
   END

If @Prop = 'MFN'
   BEGIN
     SET @SQL = @SQL + ' AND (C.DateLost <= ''' + CONVERT(VARCHAR(10), @DateMF, 111) + ''' AND P.PropertyType=''MUTUAL FUND'' AND PlanNumber = ''NETWORKED'' AND LEN(P.IraCode) = 0) '
   END

If @Prop = 'O'
   BEGIN
      SET @SQL = @SQL + ' AND (C.DateLost <= ''' + CONVERT(VARCHAR(10), @DateMF, 111) + ''' AND P.PropertyType=''OTHER'' AND LEN(P.IraCode) = 0) '  
   END

If @Prop = 'E'
   BEGIN
     SET @SQL = @SQL + '  AND (C.DateLost <= ''' + CONVERT(VARCHAR(10), @DateE, 111) + ''' AND P.PropertyType=''EQUITY'' AND LEN(P.IraCode) = 0) '  
   END

If @Agg = 'A' AND (@Prop = 'ALL' OR @Prop = 'CASH')
   BEGIN
     SET @SQL = @SQL + '  AND (C.DateLost <= ''' + CONVERT(VARCHAR(10), @DateC, 111) + '''  AND (P.CashBalance>=[S].[AggregFS] OR P.Quantity>=[S].[AggregFS]) AND P.PropertyType=''CASH'' AND LEN(P.IraCode) = 0) '      
   END

If @Agg = 'B' AND (@Prop = 'ALL' OR @Prop = 'CASH')
   BEGIN
     SET @SQL = @SQL + ' AND (C.DateLost <= ''' + CONVERT(VARCHAR(10), @DateC, 111) + ''' AND (P.CashBalance<[S].[AggregFS] AND P.Quantity<[S].[AggregFS]) AND P.PropertyType=''CASH'' AND LEN(P.IraCode) = 0) '      
   END

If @Agg Is Null AND (@Prop = 'ALL' OR @Prop = 'CASH')
   BEGIN
     SET @SQL = @SQL + ' AND (C.DateLost <= ''' + CONVERT(VARCHAR(10), @DateC, 111) + ''' AND P.PropertyType=''CASH'' AND LEN(P.IraCode) = 0) '      
   END

If @IRA Is Not Null
   BEGIN
     SET @SQL = @SQL + ' AND (C.DateOfBirth <= ''' + CONVERT(VARCHAR(10), @DateI, 111) + '''  AND LEN(P.IRACode) > 0 ) OR (LEN(C.DateOfBirth) = 0 AND LEN(C.Zip) > 0) '
   END

If @StateF Is Not Null
   BEGIN
     SET @SQL = @SQL + 'AND C.State In ' + @StateF
   END


EXEC(@SQL)
GO
zimmer9Asked:
Who is Participating?
 
rafranciscoConnect With a Mentor Commented:
Try adding a PRINT @SQL before you execute command:

PRINT @SQL
EXEC (@SQL)
GO
0
 
Brian CroweConnect With a Mentor Database AdministratorCommented:
CREATE PROCEDURE dbo.procFAllStates
@RptYear int,
@Prop varchar (3),
@Agg varchar (3),
@IRA varchar (3),
@StateF varchar(200),
@SQL varchar(8000) OUTPUT
AS
...
--remove the declaration of @SQL



0
 
ispalenyCommented:
CREATE PROCEDURE dbo.procFAllStates
@RptYear int,
@Prop varchar (3),
@Agg varchar (3),
@IRA varchar (3),
@StateF varchar(200)
@SQL2 varchar(8000)  OUTPUT

AS
....
EXEC(@SQL)
SET @SQL2= @SQL
GO

declare @SQL3 varchar(8000)
exec dbo.procFAllStates ......,@SQL3 OUTPUT
SELECT @SQL3
0
 
ispalenyConnect With a Mentor Commented:
OR

CREATE PROCEDURE dbo.procFAllStates
....
SELECT @SQL
EXEC(@SQL)
GO
exec dbo.procFAllStates ......


In QA, set Maximum characters per column to 8192 and switch results to Text format (multiline)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.