zimmer9
asked on
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.MiddleInitia l) AS [MI],
UPPER (tblCustomers.LastName) AS [Last Name], 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,
Right(tblCustomers.OfficeN umber,3) + ' ' + tblCustomers.CustomerNumbe r AS [Account Number]
FROM tblStatesAll INNER JOIN (tblCustomers INNER JOIN tblProducts ON tblCustomers.CustomerNumbe r = 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.MiddleInitia l),
(tblCustomers.LastName), (tblCustomers.Address1),(t blCustomer s.Address2 ),
(tblCustomers.Address3),(t blCustomer s.Address4 ),(tblCust omers.City ) ,
(tblCustomers.State), tblCustomers.Zip, tblCustomers.SSN,
tblCustomers.OfficeNumber, tblCustomers.CustomerNumbe r
ORDER BY tblCustomers.State
GO
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.MiddleInitia
UPPER (tblCustomers.LastName) AS [Last Name], UPPER(tblCustomers.Address
UPPER(tblCustomers.Address
UPPER(tblCustomers.State) AS State, tblCustomers.Zip AS Zip, tblCustomers.SSN,
Right(tblCustomers.OfficeN
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
(tblCustomers.DateOfBirth Is Null AND tblStatesAll.FallCycle= '1' )) AND tblStatesAll.StateFS = 'MA'
GROUP BY (tblCustomers.FirstName), (tblCustomers.MiddleInitia
(tblCustomers.LastName), (tblCustomers.Address1),(t
(tblCustomers.Address3),(t
(tblCustomers.State), tblCustomers.Zip, tblCustomers.SSN,
tblCustomers.OfficeNumber,
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.
ASKER
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
SELECT @DateFI = CAST((@RptYear - tblStatesAll.BondsFS) AS VARCHAR) + '-06-30' from tblStatesAll, tblCustomers
WHERE tblStatesAll.BondsFS = tblCustomers.State
ASKER
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
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
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
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
ASKER
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 ?