[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2005-05-10
4
Medium Priority
?
309 Views
Last Modified: 2010-03-19
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
0
Comment
Question by:zimmer9
  • 2
4 Comments
 
LVL 34

Assisted Solution

by:Brian Crowe
Brian Crowe earned 500 total points
ID: 13970333
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
 
LVL 28

Accepted Solution

by:
rafrancisco earned 1000 total points
ID: 13970337
Try adding a PRINT @SQL before you execute command:

PRINT @SQL
EXEC (@SQL)
GO
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 13970347
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
 
LVL 13

Assisted Solution

by:ispaleny
ispaleny earned 500 total points
ID: 13970382
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question