Parameters to SQL not work sometimes

[Jim B2]

:confused; Here is the stored proc. I send a value for @OrgAction for the if
statements. The first statement runs and returns data. Using the second
statement results in error message:

Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Must declare the variable
'@StartDate'.

the Org_Code code in the WHERE clause works OK in other places. I think my
date formating is correct for the view being used.

CREATE PROCEDURE dbo.spr_EvalTrnDates(
@ListItems varchar(100),
@OrgAction varchar (10),
@StartDate varchar(10)=NULL,
@EndDate varchar (10)=NULL
)
AS
SET NOCOUNT ON

/*RS1 Find people who were sent an evaluation and have not returned it. Org
Code is
in thousands groups, eg. 1000 to 1999. Sent evaluation has date range
criteria.*/


IF @OrgAction = 'EvalSent'
BEGIN
SELECT *
FROM dbo.vwGetEvalSentRptData
WHERE (Last_Eval IS NULL)AND (Eval_Notice_Sent_Date_yy BETWEEN @StartDate AND
@EndDate) AND (Org_Code LIKE @ListItems + '%')
ORDER BY Org_Code, Employee_Name

END

/*RS2 Find people who were sent an evaluation and have not returned it. Org
Code is
in a list of individual Org_Code numbers selected by the user.
Sent evaluation has date range criteria.*/


IF @OrgAction = 'EvalSentxM'
BEGIN
DECLARE @sSQLxM2 varchar(4000)
SET @sSQLxM2 = 'SELECT *
FROM dbo.vwGetEvalSentRptData
WHERE (Last_Eval IS NULL)AND (Eval_Notice_Sent_Date_yy BETWEEN @StartDate AND
@EndDate) AND
(Org_code in (''' + REPLACE(@ListItems,',',''',''') + '''))
ORDER BY Org_Code, Employee_Name'
EXEC (@sSQLxM2)
END
.......... (More of the same.)
GO

[philh]

Your second statement is dynamic SQL. The variables are not availble to the
statement at the level that EXEC() runs.

Change your SET statement:

SET @sSQLxM2 = 'SELECT *
FROM dbo.vwGetEvalSentRptData
WHERE (Last_Eval IS NULL)AND (Eval_Notice_Sent_Date_yy BETWEEN
'''+@StartDate+''' AND '''+@EndDate+''') AND
(Org_code in (''' + REPLACE(@ListItems,',',''',''') + '''))
ORDER BY Org_Code, Employee_Name'

HTH,

[Jim B2]

philh - This works perfectly. Thanks