udf brain teaser - 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

Transact-SQL (2000)

udf brain teaser

Author

Topic

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-12-15 : 05:21:42

/*
I'm getting results back when I don't think I should.
Allow me to illustrate using the northwind db.
*/

--thanks to Robvolk for the split code
--You'll need a tally table in northwind for this to work
go
CREATE FUNCTION dbo.udf_GetCustomersAndEntireFax()

RETURNS TABLE
AS
RETURN
(
select CustomerID, Fax
from customers
)
go

CREATE FUNCTION dbo.udf_GetCustomersAndFaxPartsPeriodSeperated()
RETURNS TABLE
AS
RETURN
(
SELECT CustomerID,
NullIf(SubString('.' + cast(Fax as varchar(8000)) + '.' , ID , CharIndex('.' , '.' + cast(Fax as varchar(8000)) + '.' , ID) - ID) , '') AS Word
FROM Tally, dbo.udf_GetCustomersAndEntireFax()
WHERE ID <= Len('.' + cast(Fax as varchar(8000)) + '.') AND SubString('.' + cast(Fax as varchar(8000)) + '.' , ID - 1, 1) = '.'
AND CharIndex('.' , '.' + cast(Fax as varchar(8000)) + '.' , ID) - ID > 0 --remove this line to keep NULL rows
)
go

CREATE FUNCTION dbo.udf_GetCustomersAndEntirePhone()

RETURNS TABLE
AS
RETURN
(
select CustomerID, Phone
from customers
)
go

CREATE FUNCTION dbo.udf_GetCustomersAndPhonePartsPeriodSeperated()
RETURNS TABLE
AS
RETURN
(
SELECT CustomerID,
NullIf(SubString('.' + cast(Phone as varchar(8000)) + '.' , ID , CharIndex('.' , '.' + cast(Phone as varchar(8000)) + '.' , ID) - ID) , '') AS Word
FROM Tally, dbo.udf_GetCustomersAndEntirePhone()
WHERE ID <= Len('.' + cast(Phone as varchar(8000)) + '.') AND SubString('.' + cast(Phone as varchar(8000)) + '.' , ID - 1, 1) = '.'
AND CharIndex('.' , '.' + cast(Phone as varchar(8000)) + '.' , ID) - ID > 0 --remove this line to keep NULL rows
)
go

CREATE FUNCTION dbo.udf_GetCustomersThatMatchOnPhoneAndFaxParts()
RETURNS TABLE
AS
RETURN
(
select distinct a.CustomerID
from dbo.udf_GetCustomersAndFaxPartsPeriodSeperated() A
inner join dbo.udf_GetCustomersAndPhonePartsPeriodSeperated() B on A.word = b.word
)
go

--this returns results as expected
select distinct a.CustomerID, b.companyname
from dbo.udf_GetCustomersThatMatchOnPhoneAndFaxParts() a
inner join Customers b on b.CustomerID = a.CustomerID
go

/* now simulate the problem environment */
--edit customers so that the underlying function doesnt bring back any results (or so you thought...)
update Customers set fax = replace(fax, '.', '$')
--edit records that have the same value in phone as fax
update Customers set fax = '10' where fax = phone
go

--by running this you can see that 10 <> 981-443655
select * from dbo.udf_GetCustomersAndFaxPartsPeriodSeperated() where customerid = 'warth'
select * from dbo.udf_GetCustomersAndPhonePartsPeriodSeperated() where customerid = 'warth'

--yet, this brings back results! Why???
select * from dbo.udf_GetCustomersThatMatchOnPhoneAndFaxParts()

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-12-15 : 07:03:21

Ahh,

but did you run this:
select * from dbo.udf_GetCustomersAndFaxPartsPeriodSeperated() where word = '10'
select * from dbo.udf_GetCustomersAndPhonePartsPeriodSeperated() where word = '10' --or customerID = 'WARTH'

CustomerID Word
---------- ----
WARTH      10
WILMK      10
WOLZA      10


CustomerID Word
---------- ----
FOLIG      10
LACOR      10
LAMAI      10
SPECD      10
VINET      10

rockmoose

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-12-15 : 09:27:49

what a silly melon I am!

so this fixes it:
alter FUNCTION dbo.udf_GetCustomersThatMatchOnPhoneAndFaxParts()
RETURNS TABLE
AS
RETURN
(
select distinct a.CustomerID
from dbo.udf_GetCustomersAndFaxPartsPeriodSeperated() A
inner join dbo.udf_GetCustomersAndPhonePartsPeriodSeperated() B on A.word = b.word and A.CustomerID = b.CustomerID
)
go

select * from dbo.udf_GetCustomersThatMatchOnPhoneAndFaxParts()

thanks for that. but now here comes the tricky bit. I did all this to try and reproduce a problem i am having on my dev server on "real" tables because I cant really publish the ddl and dml. But I am not able to reproduce the problem.

My udf that is similar to dbo.udf_GetCustomersAndFaxPartsPeriodSeperated causes me problems when i try and join it's results to another table.

So, if I do the equavalent of this:
select * from dbo.udf_GetCustomersThatMatchOnPhoneAndFaxParts()
it works fine

but the equavalent of this wrapped up in a sp:
select a.*
from dbo.udf_GetCustomersThatMatchOnPhoneAndFaxParts() a
inner join customers b on b.customerid = a.customerid
errors with:
Server: Msg 536, Level 16, State 3, Procedure p_MySP, Line 37
Invalid length parameter passed to the substring function.

I have this problem regardless of whether dbo.udf_GetCustomersThatMatchOnPhoneAndFaxParts() brings back results or not
Does anyone know what could be causing this? It really is doing my nut. How does doing a join cause a problem?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources