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

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 295
  • Last Modified:

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


0
zimmer9
Asked:
zimmer9
  • 4
1 Solution
 
dbeneitCommented:
¿the enter?
CREATE PROCEDURE dbo.procFAllStates
@RptYear int,
0
 
zimmer9Author Commented:
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.
0
 
dbeneitCommented:
excuse me zimmer9 is the time. I have had a stupid idea
0
 
dbeneitCommented:
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
0
 
dbeneitCommented:
in you first procedure, you uses @RptYear directly , in the second you create a string. In this moment it sholud be evaluated
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

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