May 29, 2008 at 2:49 am
All,
I have done one task recently. Here is my code,
----------------------------------------------------------------------------------------------
Create PROCEDURE dbo.s_Risk
(
@ID varchar(200),
@sp-2 Varchar(50),
@ClassId varchar(25)
)
AS
Begin
If @ID =''
Begin
Select @ID = Null
End
If @sp-2=''
Begin
Select @sp-2 = Null
End
If @ClassId =''
Begin
Select @ClassId = Null
End
Create table #ID
(
ID1 varchar(200)
)
if @ID is not null
Begin
select @ID = ','+@ID+','
insert into #ID
select substring(@ID,N+1,charindex(',',@ID,N+1)-N-1)
from Tally
where N < len(@ID)
and substring(@ID,N,1) = ','
End
Create table #Pform
(
sP varchar(50)
)
If @sp-2 is not null
Begin
select @sp-2 = ','+@sP+','
insert into #Pform
select substring(@sP,N+1,charindex(',',@sP,N+1)N-1)
from Tally
where N < len(@sP)
and substring(@sP,N,1) = ','
End
create table #ClassId
(
Class int
)
if @ClassId is not null
Begin
select @ClassId = ','+@ClassId+','
insert into #ClassId
select convert(int,substring(@ClassId,N+1,charindex(',',@ClassId,N+1)N-1)
from Tally
where N < len(@ClassId)
and substring(@ClassId,N,1) = ','
End
Select ID,
sP,
ClassId,
MoneyValue,
NetAmount,
Datestarted,
DateEnded
into #temp1
from Risk
if (@ID is not null)
begin
delete #temp1
where ID is null
or ID not in(select ID1 from #ID)
end
if (@sP is not null)
begin
delete #temp1
where sP is null
or sP not in(select sP from #Pform)
end
if (@ClassId is not null)
begin
delete #temp1
where ClassId is null
or ClassId not in(select Class from #ClassId)
end
If ( @ID = Null and @sp-2 = Null and @ClassId = Null)
Begin
Select ID,
sP,
ClassId,
MoneyValue,
NetAmount,
Datestarted,
DateEnded
from #temp1
End
Else
Begin
Select ID,
sP,
ClassId,
MoneyValue,
NetAmount,
Datestarted,
DateEnded
from #temp1
where (ID in (select ID1 from #ID) or
sP in (select sP from #Pform) or
ClassId in( select Class from #ClassId)
)
End
drop table #temp1
END
-----------------------------------------------------------------------------------------------
What the code is doing ?
Input parameter will accept comma seperated values like '01,02,03' for @ID and 'AA,BB,DD' for @sp-2
and '25,35,45' for @ClassId.
We need to split it first,then where clause should accept the splitted values and need to check against the #temp table.
why i am converting @classId value to Int ?
Because , ClassId is declared as Integer in Risk table. But we are passing it as a String.That why i have converted it.
My questions are,
1) Whether my way of approach to split the comma seperated value is correct or not.
2) is there any other way to check the splitted values in the WHERE clause ? because i am using OR clause.But i feel it is not good.
3) is there any other way to do the same task ?
(I am using sql2000)
Inputs,Suggestions and advices are always welcome !
karthik
May 29, 2008 at 4:05 am
karthikeyan (5/29/2008)
My questions are,1) Whether my way of approach to split the comma seperated value is correct or not.
2) is there any other way to check the splitted values in the WHERE clause ? because i am using OR clause.But i feel it is not good.
3) is there any other way to do the same task ?
1) Does it work? Are you comfortable with the performance? Then YES is the answer.
2) Yes. LEFT JOIN can be used.
3) Yes.
N 56°04'39.16"
E 12°55'05.25"
May 29, 2008 at 4:16 am
1) Does it work? Are you comfortable with the performance? Then YES is the answer.
2) Yes. LEFT JOIN can be used.
3) Yes.
1) Yes.It is working. But the table is having 400 rows only. But as you said , i am thinking for long way.If the table data increased rapidly i think my query will get slow. Am i correct ?
karthik
May 29, 2008 at 4:22 am
Your DELETE #temp1 statements are dangerous.
If a record does not equal to #ID it still might equal #ClassID or sP column.
So you have two diffferents sets of business rules here.
1) All three columns must match
2) At least one column must match
CREATE PROCEDURE dbo.s_Risk
(
@ID VARCHAR(200),
@sp-2 VARCHAR(50),
@ClassID VARCHAR(25)
)
AS
SET NOCOUNT ON
SELECT@ID = NULLIF(@ID, ''),
@sp-2 = NULLIF(@sP, ''),
@ClassID = NULLIF(@ClassID, '')
CREATE TABLE#ID
(
ID VARCHAR(200)
)
SET@ID = ',' + @ID + ','
INSERT#ID
SELECTDISTINCT
SUBSTRING(@ID, Number + 1, CHARINDEX(',', @ID, Number + 1) - Number - 1)
FROMTally
WHERENumber < DATALENGTH(@ID)
AND SUBSTRING(@ID, Number, 1) = ','
CREATE TABLE#pForm
(
sP VARCHAR(50)
)
INSERT#pForm
SELECTDISTINCT
SUBSTRING(@sP, Number + 1, CHARINDEX(',', @sp-2, Number + 1) - Number - 1)
FROMTally
WHERENumber < DATALENGTH(@sP)
AND SUBSTRING(@sP, Number, 1) = ','
CREATE TABLE#ClassID
(
Class INT
)
SET@ClassID = ',' + @ClassID + ','
INSERT#ClassID
SELECTDISTINCT
SUBSTRING(@ClassID, Number + 1, CHARINDEX(',', @ClassID, Number + 1) - Number - 1)
FROMTally
WHERENumber < DATALENGTH(@ClassID)
AND SUBSTRING(@ClassID, Number, 1) = ','
SELECTr.ID,
r.sP,
r.ClassID,
r.MoneyValue,
r.NetAmount,
r.Datestarted,
r.DateEnded
FROMRisk AS r
LEFT JOIN#ID
IF @ID IS NULL AND @sp-2 IS NULL AND @ClassID IS Null
SELECTID,
sP,
ClassId,
MoneyValue,
NetAmount,
Datestarted,
DateEnded
FROMRisk
ELSE
SELECTr.ID,
r.sP,
r.ClassId,
r.MoneyValue,
r.NetAmount,
r.Datestarted,
r.DateEnded
FROMRisk AS r
WHEREEXISTS (SELECT * FROM #ID AS i WHERE i.ID = r.ID)
OR EXISTS (SELECT * FROM #pForm AS p WHERE p.sP = r.sP)
OR EXISTS (SELECT * FROM #ClassID AS c WHERE c.ClassID = r.ClassID)
You can very easy change this query and replace OR EXISTS to AND EXISTS if that's your business rules.
N 56°04'39.16"
E 12°55'05.25"
May 29, 2008 at 5:07 am
SELECT r.ID,
r.sP,
r.ClassId,
r.MoneyValue,
r.NetAmount,
r.Datestarted,
r.DateEnded
FROM Risk AS r
WHERE EXISTS (SELECT * FROM #ID AS i WHERE i.ID = r.ID)
OR EXISTS (SELECT * FROM #pForm AS p WHERE p.sP = r.sP)
OR EXISTS (SELECT * FROM #ClassID AS c WHERE c.ClassID = r.ClassID)
Peso,
You also have used 'OR'.
karthik
May 29, 2008 at 5:07 am
Shall we do this with UNION ?
karthik
May 29, 2008 at 5:15 am
karthikeyan (5/29/2008)
Peso,You also have used 'OR'.
Yes, because I don't know your business rules.
Please read my previous response carefully and to the very end.
N 56°04'39.16"
E 12°55'05.25"
May 29, 2008 at 5:35 am
As you said,
My business logic is:
So you have two diffferents sets of business rules here.
1) All three columns must match
2) At least one column must match
karthik
May 29, 2008 at 6:01 am
Which is it?
You can't have both.
N 56°04'39.16"
E 12°55'05.25"
May 29, 2008 at 6:20 am
User may pass all values or sometime they won't pass any values.
If they didn't pass any value then we need to pull all the data, if they pass any value ,we need to pull the data based on the input parameter.
This is the business logic of procedure.
karthik
May 29, 2008 at 6:41 am
I understand that, BUT WHICH MATCHING preference do you want?
ID = @ID AND pF = @pF AND ClassID = @ClassID
or this
ID = @ID OR pF = @pF OR ClassID = @ClassID
N 56°04'39.16"
E 12°55'05.25"
May 29, 2008 at 6:44 am
ID = @ID OR pF = @pF OR ClassID = @ClassID obviously.
karthik
May 29, 2008 at 12:09 pm
Did you try my latest suggestion then?
N 56°04'39.16"
E 12°55'05.25"
December 24, 2009 at 3:35 pm
SwePeso (5/29/2008)
Did you try my latest suggestion then?
Heh... considering the date of that post and today's date, I'm thinking that requires some little thought, eh? 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
December 24, 2009 at 3:39 pm
Karthik,
I know this is an old post but just a couple of tips here... I noticed you have the following...
If @ID =''
Begin
Select @ID = Null
End
If @sp-2=''
Begin
Select @sp-2 = Null
End
If @ClassId =''
Begin
Select @ClassId = Null
End
That can all be replaced with the following...
--===== Convert blank parameters to nulls
SELECT @ID = NULLIF(@ID,''),
@sp-2 = NULLIF(@sP,''),
@ClassId = NULLIF(@ClassId,'')
It's no miracle of performance but it greatly simplifies the code.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply