views:

64

answers:

1

I want to write a query kind of like this:

CREATE PROCEDURE DupFinder
@FirstName varchar(20),
@LastName varchar(20)

AS

SELECT CustId 
  FROM Cust c 
 WHERE [dbo].[fn_MatchConfidence](@FirstName + ' ' + @LastName,
                                  [dbo].fn_createCustHash (CustId)) > .8

Running the fn_MatchCondifence User-Defined Function (which takes two varchars and returns a number) over the entire Cust table. CreateCustHash just makes a string of FirstName and LastName with a space in the middle.

How can I do this?

+5  A: 

Don't use a Scalar function. They're really bad.

http://msmvps.com/blogs/robfarley/archive/2009/12/05/dangers-of-begin-and-end.aspx

Instead, use an inline table-valued function.

Rob Farley
+1: Yep - indexes, gone on outermost query. Probably doing god knows within it...
OMG Ponies
Not that... the problem is that the function will be executed in a different context. Look in Profiler and you'll see the engine start a different execution context for each function call.
Rob Farley