• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 313
  • Last Modified:

Are variables created in the Stored Procedure Static values or Dynamic values in the following Stored Procedure ?

In the following store procedure, will the value of the variables DateFI, DateMF, DateC, DateE and DateI be a static Date or will their value be dynamic Dates (based on the From clause:ON tblStatesAll.StateFS = tblCustomers.State) ?


CREATE PROCEDURE dbo.procFlLN

@RptYear int
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 UPPER (tblCustomers.FirstName) AS [First Name], UPPER (tblCustomers.MiddleInitial) AS [MI],
UPPER (tblCustomers.LastName) AS [Last Name], UPPER(tblCustomers.Address1) AS [Address 1], UPPER (tblCustomers.Address2) AS [Address 2],
UPPER(tblCustomers.Address3) AS [Address 3], UPPER(tblCustomers.Address4) AS [Address 4], UPPER(tblCustomers.City) AS City,
UPPER(tblCustomers.State) AS State, tblCustomers.Zip AS Zip, tblCustomers.SSN,  
Right(tblCustomers.OfficeNumber,3) + ' ' + tblCustomers.CustomerNumber AS [Account Number]
FROM tblStatesAll INNER JOIN (tblCustomers INNER JOIN tblProducts ON tblCustomers.CustomerNumber = tblProducts.CustomerNumber)
ON tblStatesAll.StateFS = tblCustomers.State
WHERE ((tblProducts.PropertyType='OTHER')   AND tblStatesAll.FallCycle= '1'  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
               (tblCustomers.DateOfBirth Is Null AND tblStatesAll.FallCycle= '1' )) AND tblStatesAll.StateFS = 'MA'  
GROUP BY (tblCustomers.FirstName), (tblCustomers.MiddleInitial),
(tblCustomers.LastName), (tblCustomers.Address1),(tblCustomers.Address2),
(tblCustomers.Address3),(tblCustomers.Address4),(tblCustomers.City) ,
(tblCustomers.State), tblCustomers.Zip, tblCustomers.SSN,  
tblCustomers.OfficeNumber, tblCustomers.CustomerNumber  

ORDER BY tblCustomers.State
GO
0
zimmer9
Asked:
zimmer9
  • 3
  • 2
1 Solution
 
pcsentinelCommented:
You are setting the values of the dates in the

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

The where clause in the main sp is using the values of these vars as comparison so they wont get changed

regards
0
 
zimmer9Author Commented:
Let me give you more details and an example.

The tblStatesAll table is comprised of records for each state. The first few records in the tblStatesAll are as follows:

State Code         BondsFS     MutualFS       CashFS
_________         _______     _______        _______
AL                      5               3                  2  
AR                     3                4                  4
AZ                     2                2                  3

Let's say Customer record #1 read is for tblCustomers.State = 'AL'

will the SELECT @DateFI = CAST((@RptYear - tblStatesAll.BondsFS) AS VARCHAR) + '-06-30' from tblStatesAll
yield a DateFI value of 5 for tblStatesAll.BondsFS since the table customer record has a state value of 'AL'
and in the tblStatesAll table, the AL record has a value of 5 for BondsFS ?

Let's say Customer record #2 read is for tblCustomers.State = 'AZ'

will the SELECT @DateFI = CAST((@RptYear - tblStatesAll.BondsFS) AS VARCHAR) + '-06-30' from tblStatesAll
yield a DateFI value of 2 for tblStatesAll.BondsFS since the table customer record has a state value of 'AL'
and in the tblStatesAll table, the AL record has a value of 2 for BondsFS ?

If that is not the case then what will be the value for tblStatesAll.BondsFS in the variable DateFI for these 2 customer records ?

 
0
 
zimmer9Author Commented:
Would I have to modify the variable adding a WHERE clause definition ? I'm not sure of the syntax but would I need something like the following:

SELECT @DateFI = CAST((@RptYear - tblStatesAll.BondsFS) AS VARCHAR) + '-06-30' from tblStatesAll, tblCustomers
WHERE tblStatesAll.BondsFS = tblCustomers.State
0
 
zimmer9Author Commented:
Correction:

Would I have to modify the variable adding a WHERE clause definition ? I'm not sure of the syntax but would I need something like the following:

SELECT @DateFI = CAST((@RptYear - tblStatesAll.BondsFS) AS VARCHAR) + '-06-30' from tblStatesAll, tblCustomers
WHERE tblStatesAll.StateFS = tblCustomers.State
 
 
 
0
 
pcsentinelCommented:
I think I see where you are going with this. You primary var setting needs the where caluse to define the state so you would need

SELECT @DateFI = CAST((@RptYear - tblStatesAll.BondsFS) AS VARCHAR) + '-06-30' from tblStatesAll, tblCustomers
WHERE tblStatesAll.StateFS = 'MA'

to match up with your main select statement
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now