June 7, 2012 at 9:10 am
I'm having a strange problem with an ITVF. If I call it directly using
SELECT *
FROM dbo.ITVF( @ID1, @ID2 )
, it returns no results. If I copy the query that defines the ITVF out of the function into SSMS and run it directly, it returns the expected results. Is there something I'm missing here? Here's some information that may be useful:
- This doesn't happen with all our ITVFs, just this one
- Server is Windows 2003 SE SP2
- SQL 2005 SE SP3
I can't post the exact query, but the form of the query is below. I don't know how useful it will be but I'll include if for the sake of completeness:
CREATE FUNCTION dbo.ITVF(
@ID1 UNIQUEIDENTIFIER,
@ID2 UNIQUEIDENTIFIER
)
RETURNS TABLE
RETURN
SELECT
Col1,
Col2 =
CASE
WHEN SUM( A ) > 0 THEN 1
WHEN SUM( B ) > 0 THEN 1
WHEN SUM( C ) > 0 THEN 1
WHEN SUM( D ) > 0 THEN 1
WHEN SUM( E ) > 0 THEN 1
WHEN ( SUM( F ) - SUM( G ) - SUM( H ) ) > 2 THEN 1
ELSE 0
END
FROM (
SELECT
Col1,
A = ...
B = ...
C = ...
D = ...
E = ...
F = ...
G = ...
H = ...
FROM TableA
LEFT JOIN TableB
ON ...
LEFT JOIN TableC
ON ...
LEFT JOIN ItvfA
ON ...
LEFT JOIN TableD
ON ...
LEFT JOIN TableE
ON ...
LEFT JOIN TableF
ON ...
WHERE TableA.Col1 = @ID1
) AS P
GROUP BY Col1
Any thoughts or suggestions would be appreciated. Thanks.
June 7, 2012 at 9:17 am
When you call the query directly, are you using the same variable values as the values assigned to the parameters?
Kind of left guessing here because of the lack of ability to reproduce the behavior, but understand why you might not be able to post something that will do that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 7, 2012 at 9:19 am
Yes, they are both using the same values. I have the parameters declared and I call both of them from within the same SSMS window, so i know all the values are identical.
June 7, 2012 at 9:30 am
I see where you use @ID1, but what about @ID2?
June 7, 2012 at 9:34 am
It's in one of the ON clauses that I replaced with "...". I don't think the exact statement in the ON clause is very important though, because it's a LEFT JOIN and I know the base table has records matching @ID1.
June 7, 2012 at 9:36 am
Well, it is hard to help debug code when we can't see what you see.
June 7, 2012 at 9:38 am
Yea, I know. I'm trying to come up with something generic that I can post that will reproduce the issue, but in the meantime, I wanted to see if I was msising something obvious.
June 7, 2012 at 9:42 am
Only thing that could be obvious is if the @ID2 is used in a way that would may the OUTER JOINs into INNER JOINs and there was no data that matched.
June 7, 2012 at 9:46 am
OK, but remember that if I copy the query into SSMS and run it directly, it retuns all the expected results.
June 7, 2012 at 9:52 am
Recurs1on (6/7/2012)
OK, but remember that if I copy the query into SSMS and run it directly, it retuns all the expected results.
Which means there's some hidden difference between the two. A connection parameters difference (one connection with SET ANSI_NULLS ON and another with them OFF, for example); a parameter-value difference (possibly even just a typo in one query call); a connection difference (different server or database or instance); a permissions difference (not probable, but if row-level security is in use, possible); an actual query difference (not likely with copy-and-paste, but has to be considered); or something else.
There's a difference. It's just a question of figuring out what it is.
Have you tried running both the UDF and the inner query from the same SSMS connection, with a batch-separator ("GO" usually) between them? Have you done so using the login the application is using instead of your own login? Have you checked connection parameters?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 7, 2012 at 10:01 am
Which means there's some hidden difference between the two. A connection parameters difference (one connection with SET ANSI_NULLS ON and another with them OFF, for example);
I'm running them both from within the same SSMS window
a parameter-value difference (possibly even just a typo in one query call);
I have the parameters defined at the top of the windows and reuse them for both statements
a connection difference (different server or database or instance); a permissions difference (not probable, but if row-level security is in use, possible);
They both in the same SSMS window, so they're on the same connection
an actual query difference (not likely with copy-and-paste, but has to be considered); or something else.
I've copied and pasted multiple times, and verified using sp_helptext on the function being called
There's a difference. It's just a question of figuring out what it is.
That's the maddening part 🙂
Have you tried running both the UDF and the inner query from the same SSMS connection, with a batch-separator ("GO" usually) between them? Have you done so using the login the application is using instead of your own login? Have you checked connection parameters?
This function is not yet being called by an application. It's still in development, so they only place I've tried calling it is from within SSMS
June 7, 2012 at 10:07 am
In your SSMS window, does it matter which is run first? Is there a batch separator between them?
June 7, 2012 at 10:09 am
No, and no
June 7, 2012 at 10:31 am
I figured it out. Sorry everyone, but this one has had nothing to do with SQL server, and everything to do with the person behind the keyboard. I accidentally reversed the input parameters when calling the function :blush:. Thanks for all your responses.
June 7, 2012 at 11:04 am
Recurs1on (6/7/2012)
I figured it out. Sorry everyone, but this one has had nothing to do with SQL server, and everything to do with the person behind the keyboard. I accidentally reversed the input parameters when calling the function :blush:. Thanks for all your responses.
There are reasons I mentioned parameter typos as a possible difference. You're not the only person who's ever done that kind of thing. :blush:
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply