Bugged by bugs? - or - knot? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Bugged by bugs? - or - knot?

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2003-07-11 : 14:19:21

WHERE @MyCourseID IN (SELECT CourseID FROM dbo.UDF_CreditGroup(1032)

The UDF returns

1032
1032
1832
1932

The IN clause works when @MyCourseID is 1032, but fails for values 1832, 1932.

--- HOWEVER ---

When I eliminate the duplicate entry, it works properly for 1032, 1832, 1932 all the time. What's up with that?

Here's the UDF

CREATE FUNCTION dbo.UDF_CreditGroup(@CourseID INT)
returns @t table (CourseID INT)
AS
BEGIN

INSERT INTO @t (CourseID)
SELECT @CourseID -- Must return at least this CourseID
UNION ALL
SELECT CourseID
FROM CourseCredits
WHERE CreditID = (SELECT CreditID FROM CourseCredits WHERE CourseID = @CourseID)

RETURN
END

So. Did I find an SQL 2000 bug?

Meanwhile, back at the ranch, I need to find a way to guarrantee that the returned table of values returns no less than one row containing the passed CourseID, even if the CourseCredits table is empty.

Adios Amigos, away....

Sam

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-11 : 14:30:58

Sam,

I hate to be a social outcast and all, but why don't you just do:




WHERE CourseId =  @CourseID
   OR CourseId IN (
			SELECT CourseID 
			  FROM CourseCredits 
			 WHERE CreditID IN (SELECT CreditID FROM CourseCredits WHERE CourseID = @CourseID) 
		  )

?

Brett

8-)

Edited by - x002548 on 07/11/2003 14:31:38

SamC
White Water Yakist

3467 Posts

Posted - 2003-07-11 : 14:36:27

Too much recoding I guess.

I modified the UDF to read

CREATE FUNCTION dbo.UDF_CreditGroup(@CourseID INT)
returns @t table (CourseID INT)
AS
BEGIN

INSERT INTO @t (CourseID)
SELECT CourseID
FROM CourseCredits
WHERE CreditID = (SELECT CreditID FROM CourseCredits WHERE CourseID = @CourseID)

-- IF THE above SELECT returned nothing, then insert just @CourseID alone
UNION ALL
SELECT @CourseID WHERE NOT EXISTS (SELECT CourseID FROM CourseCredits WHERE CourseID = @CourseID)

RETURN
END

Sam

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-07-12 : 03:00:47

Or why not:

CREATE FUNCTION dbo.UDF_CreditGroup(@CourseID INT)
returns @t table (CourseID INT)
AS
BEGIN

INSERT INTO @t (CourseID)
SELECT @CourseID -- Must return at least this CourseID
UNION ~~ALL~~
SELECT CourseID
FROM CourseCredits
WHERE CreditID = (SELECT CreditID FROM CourseCredits WHERE CourseID = @CourseID)

RETURN
END

Owais

SamC
White Water Yakist

3467 Posts

Posted - 2003-07-12 : 09:18:08

I've never looked at UNION without ALL thanks.

Sam

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-14 : 08:51:08

UNION Returns only distinct values..

UNION ALL Returns all rows in the result set with duplicates

It doesn't say it in BOL, but in other RDBMS's UNION Returns the data sorted....

Since it doesn't say it, and sinve I haven't tested it (yet), I'm assuming non sorted.

But if that's the case, how does the optimizer Relational Engine that he got all the dups?

Hashing?

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources