UDFor SP

  • how can i create a udf or stored procedure to check if given SSN pattern is correct or not

    ssnno(123-456-7890)

    123-456-7890 - correct

    123-4567890 - not correct

    123456-7890 - not correct

    1234567890 - not correct

    123\456&8907 - not correct

  • As far as the contents of a function, or stored procedure, it would seem that the LIKE comparison operator is what you need.

    CASE

    WHEN SSSNO LIKE '[0-9][0-9][0-9][-][0-9][0-9][-][0-9][0-9][0-9][0-9]'

    THEN 'valid'

    ELSE 'invalid'

    END

  • This is definitely a case where I'd use a function not an SP. Phil has provided the code you'd be looking for.

  • Create function: http://msdn.microsoft.com/en-us/library/ms186755.aspx

    Wrap that around phil's code.

  • Hi Jack,

    How can we decide what object should we use? Just like, you said in this case you will go with function instead of stored procedure.

    Thanks

  • It really depends on how it is going to be used. This one sounds like boolean check something like:

    IF dbo.IsValidSSN(@SSN) = 1

    BEGIN

    -- Do something

    END

    ELSE

    BEGIN

    RAISERROR('Invalid Social Security Number', 16, 1)

    END

    To do that with a stored procedure I'd have to have an OUTPUT parameter, etc...

    In general if I'm returning a result to a client I'd do an SP, if I'm going to use it in a QUERY then it is probably better as a function. You do have to be careful with functions because they can be hidden RBAR (loops) and not truly set-based so I don't use a ton of functions. I think they are good in theory, but often not as good in practice. Code re-use isn't always the path to best performance in a database.

  • There are two ways to write this as a function, as a scalar function or an inline table valued function. You may actually want it both ways. One can be used as soon in Jack's post, the other as part of a CROSS APPLY in a from clause.

    Here are the two ways I wrote the function using the code provided in this thread.

    CREATE FUNCTION dbo.isValidSSN (@SSSNO CHAR(11))

    RETURNS VARCHAR(7)

    AS

    BEGIN

    RETURN(

    CASE

    WHEN @SSSNO LIKE '[0-9][0-9][0-9][-][0-9][0-9][-][0-9][0-9][0-9][0-9]'

    THEN 'valid'

    ELSE 'invalid'

    END

    )

    END;

    CREATE FUNCTION dbo.ValidSSN( @SSSNO CHAR(11))

    RETURNS TABLE

    AS

    RETURN(

    SELECT

    CASE

    WHEN @SSSNO LIKE '[0-9][0-9][0-9][-][0-9][0-9][-][0-9][0-9][0-9][0-9]'

    THEN 'valid'

    ELSE 'invalid'

    END AS ValidSSN

    );

  • Jack Corbett (1/13/2012)


    You do have to be careful with functions because they can be hidden RBAR (loops) and not truly set-based so I don't use a ton of functions. I think they are good in theory, but often not as good in practice. Code re-use isn't always the path to best performance in a database.

    Scalar functions are certainly best avoided. Multi-statement functions have occasional good uses but are again often poorly used. In-line functions are expanded into the query text just like a parameterized view would be:

    CREATE FUNCTION dbo.IsValidSSN

    (

    @Input char(11)

    )

    RETURNS TABLE

    WITH SCHEMABINDING

    AS

    RETURN

    SELECT

    CASE

    WHEN @Input LIKE '[0-9][0-9][0-9][-][0-9][0-9][-][0-9][0-9][0-9][0-9]'

    THEN CONVERT(bit, 'true')

    WHEN @Input NOT LIKE '[0-9][0-9][0-9][-][0-9][0-9][-][0-9][0-9][0-9][0-9]'

    THEN CONVERT(bit, 'false')

    ELSE

    CONVERT(bit, NULL)

    END AS IsValid;

    -- Just the same as writing the body of

    -- the function out by band

    SELECT

    ivs.IsValid

    FROM dbo.IsValidSSN('123-45-6789') AS ivs;

    GO

    SELECT

    Test.data,

    ivs.IsValid

    FROM

    (

    VALUES

    ('123-45-6789'),

    ('000-00-000'),

    ('abc-de-fghi'),

    ('987-98-9876')

    ) AS Test (data)

    CROSS APPLY dbo.IsValidSSN(Test.data) AS ivs

  • I see Lynn posted while I was writing that. In an attempt to add some value, here's a possible procedure implementation. I don't like this as much overall:

    CREATE PROCEDURE dbo.IsValidSSN

    @Input char(11),

    @IsValid bit OUTPUT

    AS

    BEGIN

    SET NOCOUNT ON

    SET @IsValid =

    CASE

    WHEN @Input LIKE '[0-9][0-9][0-9][-][0-9][0-9][-][0-9][0-9][0-9][0-9]'

    THEN CONVERT(bit, 'true')

    WHEN @Input NOT LIKE '[0-9][0-9][0-9][-][0-9][0-9][-][0-9][0-9][0-9][0-9]'

    THEN CONVERT(bit, 'false')

    ELSE

    CONVERT(bit, NULL)

    END;

    END;

    GO

    DECLARE @ValidSSN bit;

    EXECUTE dbo.IsValidSSN

    @Input = '123-45-6789',

    @IsValid = @ValidSSN OUTPUT;

    SELECT @ValidSSN;

  • Just as an aside , you may prefer to add the code to a constraint.

    http://msdn.microsoft.com/en-us/library/ms179491.aspx

    That way , if you have multiple ways data ( different procs, an ssis task or two etc..) can be inserted, then the database will ensure the data is in the correct format.



    Clear Sky SQL
    My Blog[/url]

  • This is a good option, so long as the temptation to use a scalar function in the constraint definition is resisted. Write the condition out in full in the CHECK constraint:

    DECLARE @SSNs TABLE

    (

    SSN char(11) NOT NULL

    CHECK (SSN LIKE '[0-9][0-9][0-9][-][0-9][0-9][-][0-9][0-9][0-9][0-9]')

    )

  • SQL Kiwi (1/14/2012)


    This is a good option, so long as the temptation to use a scalar function in the constraint definition is resisted. Write the condition out in full in the CHECK constraint:

    DECLARE @SSNs TABLE

    (

    SSN char(11) NOT NULL

    CHECK (SSN LIKE '[0-9][0-9][0-9][-][0-9][0-9][-][0-9][0-9][0-9][0-9]')

    )

    I'd actually recommend doing both a function and this constraint. I'd do the function to verify data BEFORE the insert or update into the table is done. I'd call this defense in depth. You are verifying data as part of the business process and then you are enforcing the integrity of the data with the check constraint in case there are instances where the data is touched outside the application, like a DBA doing an insert or update (not that I've ever done this :-D).

  • Jack Corbett (1/14/2012)


    I'd actually recommend doing both a function and this constraint. I'd do the function to verify data BEFORE the insert or update into the table is done. I'd call this defense in depth. You are verifying data as part of the business process and then you are enforcing the integrity of the data with the check constraint in case there are instances where the data is touched outside the application, like a DBA doing an insert or update (not that I've ever done this :-D).

    Yes, I like my constraints to be as close to the data as possible too. It's a shame CHECK constraints cannot use in-line functions, otherwise we could use that rather than duplicating the CHECK logic on any table with an SSN. Of course the format of SSNs doesn't change much, but there are other cases where the CHECK logic might be changed from time to time.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply