Help! scalar function returns data in TSQL, but not in ASP

Question

Help! scalar function returns data in TSQL, but not in ASP

Pete T-366679

SSCrazy

Points: 2065
More actions
October 22, 2007 at 11:44 am

#98674

I'm working on a classic ASP app, connecting to a development SQL 2005 database. However, the production db is on SQL2000, but I don't think that matters.
I have a scalar function that returns a varchar(8000) value. I have a statement in ASP that runs this function, and is suppose to return certain data. When it's in ASP, it is not returning the right data. If I response.write out the SQL statement, copy and paste it into query analyzer, the function performs correctly. I am connected to query analyzer with the same SQL user that the ASP code is executing as.
This function performs some logic and returns data. It has an initial query, which if it finds something, it returns that result. If it doesn't find anything, it drops out and returns different data. From the Profiler, it appears that when I run it from the ASP page it is not finding data in the first query, and so is dropping out to the later results. But if I run the same statement from within QA, it works correctly.
Any ideas on what might be going on? Thanks for any help....I've been racking my brain all day on this!! Gahhhh!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

Pete -

What is wrong about what you see. It would probably be helpful to see what you're sending and what you're getting back, so we can help you decipher what's going on. There are too many "moving parts" right now for a decent answer without any specifics.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Pete T-366679 SSCrazy Points: 2065 More actions · Answer 2

Thanks for the reply, and let me see if I can get some specific code about whats going on.

I've narrowed it down to this section of code:

SELECT DISTINCT @GetAILanguage = COALESCE(strAddlInsdLang , '')
FROM tblReqMatrix INNER JOIN tblReqCoverages ON tblReqMatrix.lngReqID_pk = tblReqCoverages.lngReqID_pk
INNER JOIN vueAccountCoverages ON vueAccountCoverages.strCode = tblReqCoverages.strCovCode
WHERE tblReqCoverages.strCovCode = @strCovCode
AND vueAccountCoverages.strCodeType = 'Coverage'
AND vueAccountCoverages.lngAccountID_pk = @lngAcct
AND tblReqCoverages.strAddlInsdLang > ''
AND tblReqMatrix.lngInsuredsID_pk = @lngInsd
--RETURN @GetAILanguage
IF @GetAILanguage != ''
BEGIN
RETURN @GetAILanguage
END

This is a chunk of code that is in the function. @GetAILanguage should always return something if it is in this section. If I run this in query analyzer, it works correctly and returns proper data. If I execute the function from ASP code, or execute this *same* query from an ASP page, it is not returning any results. In the ASP page, I'm printing the SQL statement before executing it, and can copy and paste that into query analyzer and get data, but when it runs from ASP it's not returning anything.

Both methods of testing are running as the same sql user, and I've checked all the permissions and everything to make sure they're correct. Does this help any? Thanks!

Pete T-366679 SSCrazy Points: 2065 More actions · Answer 3

Gahhhhhh, it's an ID10T error, on me :-/ The order of procedures in my code was causing a valueto be cleared out that this function depends on. It wasn't creating an error, it was just not returning the correct data. But that value was set later on, so when I'd try and run it outside of the ASP page everything would work. Sigh......its only Monday, too.

Thanks everyone, and sorry for wasting your time!

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

I'm thinking you probably want to use an output parameter, instead of messing with the return code.

Looks something like this:

create procedure blah (existing param list.....,@MYOUTPUT varchar(50)= null OUTPUT)

as

.....

SELECT DISTINCT @GetAILanguage = COALESCE(strAddlInsdLang , '')

FROM tblReqMatrix INNER JOIN tblReqCoverages ON tblReqMatrix.lngReqID_pk = tblReqCoverages.lngReqID_pk

INNER JOIN vueAccountCoverages ON vueAccountCoverages.strCode = tblReqCoverages.strCovCode

WHERE tblReqCoverages.strCovCode = @strCovCode

AND vueAccountCoverages.strCodeType = 'Coverage'

AND vueAccountCoverages.lngAccountID_pk = @lngAcct

AND tblReqCoverages.strAddlInsdLang > ''

AND tblReqMatrix.lngInsuredsID_pk = @lngInsd

--RETURN @GetAILanguage

IF @GetAILanguage != ''

BEGIN

select @myoutput=@GetAILanguage

END

....

then when you use that as in this example , by specifying @myOUTPUT OUTPUT (meaning - what I get back after the SP finishes), you can retrieve the value.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?