I have a stored procedure that does a qc check on 2 tables. It contains a
case statement that checks to see if a value falls in a certain range. If it
does it returns a success message, if it fails it returns a failure message.
The code is below

Select Case When
((SELECT SUM_KEY_FIELD FROM tbl_QC_Verify WHERE [FILE_NAME] =
''ADRP_DECISIONED'' ) BETWEEN
( SELECT SUM(FORIGAMOUNT) - COUNT(*) * .99 FROM
TBL_CBDECISIONINFO_ADRP_STAGE ) AND
( SELECT SUM(FORIGAMOUNT) + COUNT(*) * .99 FROM
TBL_CBDECISIONINFO_ADRP_STAGE ))
THEN ''QCVerify Complete for Sum Comparison on TBL_CBDECISIONINFO_ADRP''
ELSE ''QCVerify Failed for Sum Comparison on TBL_CBDECISIONINFO_ADRP''
END)


This is not the whole procedure, but I have an output parameter which
returns the result of the case statement. I run this in Query Analyzer and I
get the desired result. When I put this into a DTS package and have the
output assigned to a Global Variable in the package, the variable comes up as
non displayable.

The confusing part is this. It works fine in Query Analyzer, but not in
DTS. If I alter the stored procedure and replace the select statements with
numbers so I basically have

Select Case When
((5 ) BETWEEN
(10) AND
( 2))
THEN ''QCVerify Complete for Sum Comparison on TBL_CBDECISIONINFO_ADRP''
ELSE ''QCVerify Failed for Sum Comparison on TBL_CBDECISIONINFO_ADRP''
END)

If I run this in DTS now, the global variable is populated with the desired
result. Why is the DTS pacakge behaving this way? Has anybody else ran into
this kind of problem?

The code I am using to run the procedure in both Query Analyzer and DTS is

Declare @QCStatus VarChar(8000)
EXEC usp_QCVerify @QCStatus output
select @QCStatus As QCStatus

Any help is appreciated as I have spent many hours trying to figure this out.

Thanks!!!

Have you followed this article?

Global Variables and Stored Procedure Parameters
(http://www.sqldts.com/default.aspx?234)

also have you set

SET NOCOUNT ON

at the top of the proc?

--



Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know


"Andy" <Andy (AT) discussions (DOT) microsoft.com> wrote

Yes I have nocount on. Also the DTS package correctly assings a value to the
global variable if I hard code numbers into my case statement, as shown in my
original post. If I keep the select statements in there then it does not
output a value to the global variable. The behavior I am getting is very
odd. I have had co-workers look at it and they are just as lost as I am.

"Allan Mitchell" wrote:

Can you build up the statement and see where the output value gets lost
eventually?

--



Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know


"Andy" <Andy (AT) discussions (DOT) microsoft.com> wrote

It appears it does not like the sum function. If I run this

Select Case When
((SELECT SUM_KEY_FIELD FROM tbl_QC_Verify WHERE [FILE_NAME] =
''ADRP_DECISIONED'' ) BETWEEN
( SELECT 5000000 - COUNT(*) * .99 FROM TBL_CBDECISIONINFO_ADRP_STAGE ) AND
( SELECT 5000000 + COUNT(*) * .99 FROM TBL_CBDECISIONINFO_ADRP_STAGE ))
THEN ''QCVerify Complete for Sum Comparison on TBL_CBDECISIONINFO_ADRP''
ELSE ''QCVerify Failed for Sum Comparison on TBL_CBDECISIONINFO_ADRP''
END


It works, but if I run this

Select Case When
((SELECT SUM_KEY_FIELD FROM tbl_QC_Verify WHERE [FILE_NAME] =
''ADRP_DECISIONED'') BETWEEN
( SELECT SUM(FORIGAMOUNT) - COUNT(*) * .99 FROM
TBL_CBDECISIONINFO_ADRP_STAGE ) AND
( SELECT SUM(FORIGAMOUNT) + COUNT(*) * .99 FROM
TBL_CBDECISIONINFO_ADRP_STAGE ))
THEN ''QCVerify Complete for Sum Comparison on TBL_CBDECISIONINFO_ADRP''
ELSE ''QCVerify Failed for Sum Comparison on TBL_CBDECISIONINFO_ADRP''
END

it doesn't work. It looks the only difference is I took out the
Sum(FORIGAMOUNT) and put a hard coded number in there.





"Allan Mitchell" wrote:

OK So can you not calculate things up front and present a less sophisticated
rowset to the proc?

--



Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know


"Andy" <Andy (AT) discussions (DOT) microsoft.com> wrote

That was my next step. I tried created a variable first and passing it into
the sql statement and it still failed. I have no idea why. Thanks for your
help on this though.

"Allan Mitchell" wrote: