August 4, 2008 at 2:03 am
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
August 4, 2008 at 2:58 am
You can use patindex, like:
if patindex('[10C],[10C],[10C]', @ip) = 1
Regards,
Andras
August 4, 2008 at 4:11 am
More ideas are welcome !
karthik
August 4, 2008 at 4:26 am
Andras,
Thanks for your idea.Your method is working fine.
karthik
August 4, 2008 at 5:00 am
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
August 4, 2008 at 5:08 am
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
August 4, 2008 at 5:22 am
Andras,
It is working perfectly. Thanks a lot !
karthik
August 4, 2008 at 5:24 am
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
August 4, 2008 at 5:47 am
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
August 4, 2008 at 6:10 am
Andras,
Wonderful explanation ! It is really very nice !
My requireent is just to check out the input parameter values.
karthik
August 4, 2008 at 6:15 am
Andras,
Can you post both the query plan method ?
karthik
August 4, 2008 at 6:44 am
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
August 4, 2008 at 7:09 am
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
August 4, 2008 at 7:31 am
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
August 4, 2008 at 8:01 am
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