zimmer9
asked on
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].[Aggre gFS] 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].[Aggreg FS] 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
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].[Aggre
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].[Aggreg
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@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