Passing a column to a UDF

Question

I have a table which has a lot of SSN's and that needs to be passed to a UDF that would validate it and say if its valid or not.

For example, when I execute the following query I get an error:

SELECT [dbo].[f_Is_Valid_SSN_Regex]( (select SSN from dbo.table_name))

The error that I get

Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

I dont think this requires a cursor (I would hate to have that), but is there a way to validate all the records on that column using this function?

Thanks,

RV

Answer 1

You would use a regular select for this:

SELECT SSN, [dbo].f_Is_Valid_SSN_Regex(SSN)
from dbo.table_name;

If you want to see if all of them pass, then use aggregation:

SELECT MIN(dbo.f_Is_Valid_SSN_Regex(SSN))
from dbo.table_name;

If the function return 0 for fails and 1 for passes, then this will return 1 only if all pass.