zimmer9
asked on
See output of executing a stored procedure via Query Analyzer ?
If I execute the following Stored Procedure via the SQL Query Analyzer, I get the following 1 line message:
The command(s) completed successfully.
-------------------------- ---------- ---------- -----
Is there a way I can see the result set when I execute this Stored Procedure (via Execute Query button or hitting the F5 key).
ALTER PROCEDURE dbo.procFlACS
@RptYear int = 2005
AS
DECLARE @DateFI AS DATETIME
DECLARE @DateMF AS DATETIME
DECLARE @DateC AS DATETIME
DECLARE @DateE AS DATETIME
DECLARE @DateI AS DATETIME
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
SELECT Null AS Title, UPPER(tblCustomers.FirstNa me) AS [First Name], UPPER(tblCustomers.MiddleI nitial) AS [MI],
UPPER(tblCustomers.LastNam e) AS [Last Name], UPPER(tblCustomers.SecondN ameFirst) AS [Second Name First],
UPPER(tblCustomers.SecondN ameMid) AS [Second Name Mid], UPPER(tblCustomers.SecondN ameLast) AS [Second Name Last],
Null AS Relation, UPPER(tblCustomers.Address 1) AS [Address 1], UPPER (tblCustomers.Address2) AS [Address 2],
UPPER(tblCustomers.Address 3) AS [Address 3], UPPER(tblCustomers.Address 4) AS [Address 4], UPPER(tblCustomers.City) AS City,
UPPER(tblCustomers.State) AS State, tblCustomers.Zip AS Zip, tblCustomers.SSN, Null AS [DDA Number], Null AS [Check/Cert Number],
tblProducts.CUSIP, tblProducts.SecurityName AS [Security Name], Null AS [Sub-Issue],
Right(tblCustomers.OfficeN umber,3) + ' ' + tblCustomers.CustomerNumbe r AS [Account Number],
tblProducts.PropertyType AS [Property Type], Null AS [Property Status],
CASE WHEN tblProducts.CashBalance IS NULL THEN tblProducts.Quantity ELSE tblProducts.CashBalance END AS [Check/Cert Amount]
FROM tblStatesAll INNER JOIN (tblCustomers INNER JOIN tblProducts ON tblCustomers.CustomerNumbe r = tblProducts.CustomerNumber )
ON tblStatesAll.StateFS = tblCustomers.State
WHERE (tblProducts.PropertyType= 'OTHER' AND tblCustomers.DateLost <= @DateMF AND tblStatesAll.FallCycle= '1' AND LEN(tblProducts.IRACode) = 0) OR
(tblProducts.PropertyType= 'MUTUAL FUND' AND tblCustomers.DateLost <= @DateMF AND tblStatesAll.FallCycle= '1' AND LEN(tblProducts.IRACode) = 0) OR
(tblProducts.PropertyType= 'CASH' AND tblCustomers.DateLost <= @DateC AND tblStatesAll.FallCycle= '1' AND LEN(tblProducts.IRACode) = 0) OR
(tblProducts.PropertyType= 'EQUITY' AND tblCustomers.DateLost <= @DateE AND tblStatesAll.FallCycle= '1' AND LEN(tblProducts.IRACode) = 0) OR
(tblProducts.PropertyType= 'FIXED INCOME' AND tblCustomers.DateLost <= @DateFI AND tblStatesAll.FallCycle= '1' AND LEN(tblProducts.IRACode) = 0) OR
(tblCustomers.DateOfBirth <= @DateI AND tblStatesAll.FallCycle= '1' AND LEN(tblProducts.IRACode) > 0) OR
(LEN(tblCustomers.DateOfBi rth) = 0 AND tblStatesAll.FallCycle= '1' )
ORDER BY tblCustomers.State
GO
The command(s) completed successfully.
--------------------------
Is there a way I can see the result set when I execute this Stored Procedure (via Execute Query button or hitting the F5 key).
ALTER PROCEDURE dbo.procFlACS
@RptYear int = 2005
AS
DECLARE @DateFI AS DATETIME
DECLARE @DateMF AS DATETIME
DECLARE @DateC AS DATETIME
DECLARE @DateE AS DATETIME
DECLARE @DateI AS DATETIME
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
SELECT Null AS Title, UPPER(tblCustomers.FirstNa
UPPER(tblCustomers.LastNam
UPPER(tblCustomers.SecondN
Null AS Relation, UPPER(tblCustomers.Address
UPPER(tblCustomers.Address
UPPER(tblCustomers.State) AS State, tblCustomers.Zip AS Zip, tblCustomers.SSN, Null AS [DDA Number], Null AS [Check/Cert Number],
tblProducts.CUSIP, tblProducts.SecurityName AS [Security Name], Null AS [Sub-Issue],
Right(tblCustomers.OfficeN
tblProducts.PropertyType AS [Property Type], Null AS [Property Status],
CASE WHEN tblProducts.CashBalance IS NULL THEN tblProducts.Quantity ELSE tblProducts.CashBalance END AS [Check/Cert Amount]
FROM tblStatesAll INNER JOIN (tblCustomers INNER JOIN tblProducts ON tblCustomers.CustomerNumbe
ON tblStatesAll.StateFS = tblCustomers.State
WHERE (tblProducts.PropertyType=
(tblProducts.PropertyType=
(tblProducts.PropertyType=
(tblProducts.PropertyType=
(tblProducts.PropertyType=
(tblCustomers.DateOfBirth <= @DateI AND tblStatesAll.FallCycle= '1' AND LEN(tblProducts.IRACode) > 0) OR
(LEN(tblCustomers.DateOfBi
ORDER BY tblCustomers.State
GO
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.