Procedure parameter help

  • Hi All,

    One of co worker came to my seat and explained his requirement.

    I have a procedure test with input parameter as ''0,1,C' (This is single parameter having value '0,1,C' )

    I can pass value like '0,1,C' or '1,0,C' likewise. anything other than This VALUES .. suppose '0,2,C' or '1,2,K

    IT should through an exception. we should check it with single query itself.

    Single parameter should contain 0,1,c value only it can be interchanged like '1,0,C' ALSO.

    I suggested him to use the following method.

    Create proc p1

    (

    @ip1 varchar(10)

    )

    IF substring(@ip,1,1) in (‘0’,’1’,’C’) and

    substring(@ip,3,1) in (‘0’,’1’,’C’) and substring(@ip,5,1) in (‘0’,’1’,’C’)

    Begin

    Print 'Success....'

    End

    Else

    Begin

    Print 'Exception...'

    End

    Any other approach would be warmly welcome !

    karthik

  • You can use patindex, like:

    if patindex('[10C],[10C],[10C]', @ip) = 1

    Regards,

    Andras


    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • More ideas are welcome !

    karthik

  • Andras,

    Thanks for your idea.Your method is working fine.

    karthik

  • A small modification in the requirement.

    the same character should not repeat twice. if the same character repeat twice then we need to throw the failure error message.

    say for example,

    0,0,C (or)

    0,C,0 (or)

    1,1,C (or)

    C,1,1

    ...

    ...

    ...

    ...

    karthik

  • karthikeyan (8/4/2008)


    A small modification in the requirement.

    the same character should not repeat twice. if the same character repeat twice then we need to throw the failure error message.

    say for example,

    0,0,C (or)

    0,C,0 (or)

    1,1,C (or)

    C,1,1

    ...

    ...

    ...

    ...

    What about:

    PATINDEX('[10C],[10C],[10C]', @ip) = 1

    AND CHARINDEX('1', @ip) > 0

    AND CHARINDEX('0', @ip) > 0

    AND CHARINDEX('C', @ip) > 0

    Andras


    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • Andras,

    It is working perfectly. Thanks a lot !

    karthik

  • Andras,

    How about my method ?

    -----------------------------------

    Declare @Str varchar(50),@Cnt int

    select @Str = '1,C,C'

    select @Cnt = count(*) from

    (

    select ascii(substring(@Str,1,1))

    union

    select ascii(substring(@Str,3,1))

    union

    select ascii(substring(@Str,5,1))

    ) a

    if patindex('[01C],[01C],[01C]',@Str) =1

    Begin

    If @Cnt = 3

    Begin

    Print 'Success...'

    End

    else

    Begin

    Print 'Failure...'

    End

    End

    ---------------------------------------

    karthik

  • karthikeyan (8/4/2008)


    Andras,

    How about my method ?

    -----------------------------------

    Declare @Str varchar(50),@Cnt int

    select @Str = '1,C,C'

    select @Cnt = count(*) from

    (

    select ascii(substring(@Str,1,1))

    union

    select ascii(substring(@Str,3,1))

    union

    select ascii(substring(@Str,5,1))

    ) a

    ...

    ---------------------------------------

    I did a small test on a larger table and compared the actual execution plans of the two methods. The charindex method was about 100 times faster than to one with the select..union stuff. The main reason is that union solution does several merge joins (for the union), then an aggregate, and then ... whereas the charindex simply does a logical expression evaluation.

    However, in my test case I had hundreds of thousands of items. If you are executing your stored procedure rarely, a single parameter check will not influence performance too much. In this case go for the code that you think is more maintainable, easier to read, can be easier to understand.

    Regards,

    ANdras


    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • Andras,

    Wonderful explanation ! It is really very nice !

    My requireent is just to check out the input parameter values.

    karthik

  • Andras,

    Can you post both the query plan method ?

    karthik

  • karthikeyan (8/4/2008)


    Andras,

    Can you post both the query plan method ?

    This is what I used. After executing the create table and the inserts, just select the last two select statements, and have a look at the actual execution plans (In SSMS Query->Include Actual Execution Plans)) (Note that this is not a perfect way of comparing the two)

    CREATE TABLE #foo1 ( ip VARCHAR(5) )

    INSERT INTO #foo1 VALUES ( '1,1,C' )

    INSERT INTO #foo1 VALUES ( '0,1,C' )

    INSERT INTO #foo1 VALUES ( '0,1,C' )

    INSERT INTO #foo1 VALUES ( '0,1,C' )

    go 100000

    SELECT COUNT(*)

    FROM #foo1

    WHERE PATINDEX('[10C],[10C],[10C]', ip) = 1

    AND CHARINDEX('1', ip) > 0

    AND CHARINDEX('0', ip) > 0

    AND CHARINDEX('C', ip) > 0

    SELECT COUNT(*)

    FROM #foo1

    WHERE 3 = ( SELECT COUNT(*)

    FROM ( SELECT ASCII(SUBSTRING(ip, 1, 1))

    UNION

    SELECT ASCII(SUBSTRING(ip, 3, 1))

    UNION

    SELECT ASCII(SUBSTRING(ip, 5, 1))

    ) AS b ( s )

    )

    Regards,

    Andras


    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • Andras,

    Thanks a lot for your prompt reply ! So when we think performance we need to use your method, when we think maintanance we can use my method. Am i right ?

    karthik

  • karthikeyan (8/4/2008)


    Andras,

    Thanks a lot for your prompt reply ! So when we think performance we need to use your method, when we think maintanance we can use my method. Am i right ?

    Personally I find the CHARINDEX solution simpler and easier to maintain. But if you feel that your solution is easier to understand for you, and the performance is acceptable for you, it is definitely an alternative. At the end of the day you will need to maintain it 🙂

    Regards,

    Andras


    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • Andras,

    Nice comments ! Thank you !

    karthik

Viewing 15 posts - 1 through 15 (of 15 total)

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