asked on

Pass the same parameter to 2 different Stored Procedures. 1 works and the other fails.

I executed the following from Query Analyzer and it works fine.

EXEC dbo.procFlIRA 2005
-----------------------------------------
CREATE PROCEDURE dbo.procFlIRA
@RptYear int
AS
if exists(select * from dbo.sysobjects where name = 'tblFlIRA' and type = 'U')
drop table tblFlIRA
SELECT Null AS Title, UPPER(tblCustomers.FirstName) AS [First Name], UPPER(tblCustomers.MiddleInitial) AS [MI],
UPPER(tblCustomers.LastName) AS [Last Name], UPPER(tblCustomers.SecondNameFirst) AS [Second Name First],
UPPER(tblCustomers.SecondNameMid) AS [Second Name Mid], UPPER(tblCustomers.SecondNameLast) AS [Second Name Last],
Null AS Relation, 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, Null AS [DDA Number], Null AS [Check/Cert Number],
tblProducts.CUSIP, tblProducts.SecurityName AS [Security Name], Null AS [Sub-Issue],
Right(tblCustomers.OfficeNumber,3) + ' ' + tblCustomers.CustomerNumber AS [Account Number],
tblProducts.PropertyType AS [Property Type], Null AS [Property Status],
CASE WHEN LEN(tblProducts.CashBalance) = 0 THEN tblProducts.Quantity ELSE tblProducts.CashBalance END AS [Check/Cert Amount], tblCustomers.DateOfBirth
INTO tblFlIRA
FROM tblStatesAll INNER JOIN (tblCustomers INNER JOIN tblProducts ON tblCustomers.CustomerNumber = tblProducts.CustomerNumber)
ON tblStatesAll.StateFS = tblCustomers.State
WHERE ((tblCustomers.DateOfBirth <= CAST(((@RptYear - tblStatesAll.IRAFS)-71) AS VARCHAR) + '-12-31' AND tblStatesAll.FallCycle= 1 AND LEN(tblProducts.IRACode) > 0)) OR
((LEN(tblCustomers.DateOfBirth) = 0 AND tblStatesAll.FallCycle= 1 AND LEN(tblProducts.IRACode) > 0)) AND tblStatesAll.StateFS = 'MA'
GO

I executed the following from Query Analyzer and it gives me the following message:
I use the same parameter, @RptYear, in both stored procedures and yet one stored procedure works and the other doesn't.
Do you know why this is the case ?

exec dbo.procFAllStates 2005,'ALL',NULL,'ALL'

Server: Msg 137, Level 15, State 2, Line 8
Must declare the variable '@RptYear'.
----------------------------------------------------
CREATE PROCEDURE dbo.procFAllStates

@RptYear int,
@PropF varchar (3),
@Agg varchar (3),
@IRA varchar (3)

AS
DECLARE

@SQL Varchar(8000)
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 @PropF = 'ALL'
BEGIN
SET @SQL = @SQL + ' AND ((C.DateLost <= (DateAdd(YEAR,-1 * [S].[BondsFS],CAST(@RptYear as char(4)) + ''-06-30'')) AND P.PropertyType=''FIXED INCOME'' AND LEN(P.IraCode) = 0) '
SET @SQL = @SQL + ' OR (C.DateLost <= (DateAdd(YEAR,-1 * [S].[MutualFS],CAST(@RptYear as char(4)) + ''-06-30'')) AND P.PropertyType=''MUTUAL FUND'' AND PlanNumber = NETWORKED AND LEN(P.IraCode) = 0) '
SET @SQL = @SQL + ' OR (C.DateLost <= (DateAdd(YEAR,-1 * [S].[MutualFS],CAST(@RptYear as char(4)) + ''06-30'')) AND P.PropertyType=''MUTUAL FUND'' AND P.PlanNumber <> ''NETWORKED'' AND LEN(P.IraCode) = 0) '
SET @SQL = @SQL + ' OR (C.DateLost <= (DateAdd(YEAR,-1 * [S].[MutualFS],CAST(@RptYear as char(4)) + ''-06-30'')) AND P.PropertyType=''OTHER'' AND LEN(P.IraCode) = 0) '
SET @SQL = @SQL + ' OR (C.DateLost <= (DateAdd(YEAR,-1 * [S].[StocksFS],CAST(@RptYear as char(4)) + ''-06-30'')) AND P.PropertyType=''EQUITY'' AND LEN(P.IraCode) = 0)) '
END

If @PropF = 'B'
BEGIN
SET @SQL = @SQL + ' AND (C.DateLost <= (DateAdd(YEAR,-1 * [S].[BondsFS],CAST(@RptYear as char(4)) + ''-06-30'')) AND P.PropertyType=''FIXED INCOME'' AND LEN(P.IraCode) = 0) '
END

If @PropF = 'MFA'
BEGIN
SET @SQL = @SQL + ' AND (C.DateLost <= (DateAdd(YEAR,-1 * [S].[MutualFS],CAST(@RptYear as char(4)) + ''06-30'')) AND P.PropertyType=''MUTUAL FUND'' AND P.PlanNumber <> ''NETWORKED'' AND LEN(P.IraCode) = 0) '
END

If @PropF = 'MFN'
BEGIN
SET @SQL = @SQL + ' AND (C.DateLost <= (DateAdd(YEAR,-1 * [S].[MutualFS],CAST(@RptYear as char(4)) + ''-06-30'')) AND P.PropertyType=''MUTUAL FUND'' AND PlanNumber = NETWORKED AND LEN(P.IraCode) = 0) '
END

If @PropF = 'O'
BEGIN
SET @SQL = @SQL + ' AND (C.DateLost <= (DateAdd(YEAR,-1 * [S].[MutualFS],CAST(@RptYear as char(4)) + ''-06-30'')) AND P.PropertyType=''OTHER'' AND LEN(P.IraCode) = 0) '
END

If @PropF = 'E'
BEGIN
SET @SQL = @SQL + ' AND (C.DateLost <= (DateAdd(YEAR,-1 * [S].[StocksFS],CAST(@RptYear as char(4)) + ''-06-30'')) AND P.PropertyType=''EQUITY'' AND LEN(P.IraCode) = 0) '
END

If @Agg = 'A' AND (@PropF = 'ALL' OR @PropF = 'CASH')
BEGIN
SET @SQL = @SQL + ' AND (C.DateLost <= (DateAdd(YEAR,-1 * [S].[CashFS],CAST(@RptYear as char(4)) + ''-06-30'')) " AND (P.CashBalance>=[S].[AggregFS] OR P.Quantity>=[S].[AggregFS]) AND P.PropertyType=''CASH'' AND LEN(P.IraCode) = 0) '
END

If @Agg = 'B' AND (@PropF = 'ALL' OR @PropF = 'CASH')
BEGIN
SET @SQL = @SQL + ' AND (C.DateLost <= (DateAdd(YEAR,-1 * [S].[CashFS],CAST(@RptYear as char(4)) + ''-06-30'')) " 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 (@PropF = 'ALL' OR @PropF = 'CASH')
BEGIN
SET @SQL = @SQL + ' AND (C.DateLost <= (DateAdd(YEAR,-1 * [S].[CashFS],CAST(@RptYear as char(4)) + ''-06-30'')) AND P.PropertyType=''CASH'' AND LEN(P.IraCode) = 0) '
END

If @IRA Is Not Null
BEGIN
SET @SQL = @SQL + ' AND (C.DateOfBirth <= (DateAdd(YEAR,-1*[S].[IRAFS]-71,CAST(@RptYear as char(4)) + ''-12-31'')) AND LEN(P.IRACode) > 0 ) OR (LEN(C.DateOfBirth) = 0 AND LEN(C.Zip) > 0) '
END

EXEC(@SQL)
GO

dbeneit

¿the enter?
CREATE PROCEDURE dbo.procFAllStates
@RptYear int,

zimmer9

ASKER

I tried to move the parameter up 1 line as you suggested but the results are the same.
I think the spacing is just a question of style and has no effect. Thanks anyway.

dbeneit

excuse me zimmer9 is the time. I have had a stupid idea

dbeneit

create PROCEDURE au_info1

@lastname int,
@firstname varchar(20)
AS
DECLARE

@SQL Varchar(8000)

Set @SQL ='SELECT CAST ( @lastname as char(3)) as hfjg'

EXEC(@SQL)

alter PROCEDURE au_info2

@lastname int,
@firstname varchar(20)
AS
DECLARE

@SQL Varchar(8000)

Set @SQL ='SELECT ' + CAST ( @lastname as char(3))
Set @SQL = @SQL + ' as hfjg'

EXEC(@SQL)

EXECUTE au_info2 5, 'Ann'
EXECUTE au_info1 5, 'Ann'

error in au_info1. this is because you should construct the string. @RptYear not exist in the select context

ASKER CERTIFIED SOLUTION

dbeneit

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial