0

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

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
rvphx
  • 2,324
  • 6
  • 40
  • 69

1 Answers1

2

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Excellent. This works very well. Although I am getting all 0's only for now. But that could be a separate issue. But for now, this issue is resolved. Thanks very much Gordon! – rvphx Oct 19 '15 at 02:38