SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Tally Table - Splitted values


Tally Table - Splitted values

Author
Message
karthik M
karthik M
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13163 Visits: 2588
All,


I have done one task recently. Here is my code,
----------------------------------------------------------------------------------------------
Create PROCEDURE dbo.s_Risk
(
@ID varchar(200),
@sP Varchar(50),
@ClassId varchar(25)
)
AS
Begin

If @ID =''
Begin
Select @ID = Null
End

If @sP=''
Begin
Select @sP = 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 is not null
Begin
select @sP = ','+@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 = 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
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
SwePeso
SwePeso
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18585 Visits: 3433
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"
karthik M
karthik M
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13163 Visits: 2588
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
SwePeso
SwePeso
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18585 Visits: 3433
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 VARCHAR(50),
@ClassID VARCHAR(25)
)
AS

SET NOCOUNT ON

SELECT @ID = NULLIF(@ID, ''),
@sP = NULLIF(@sP, ''),
@ClassID = NULLIF(@ClassID, '')

CREATE TABLE #ID
(
ID VARCHAR(200)
)

SET @ID = ',' + @ID + ','

INSERT #ID
SELECT DISTINCT
SUBSTRING(@ID, Number + 1, CHARINDEX(',', @ID, Number + 1) - Number - 1)
FROM Tally
WHERE Number < DATALENGTH(@ID)
AND SUBSTRING(@ID, Number, 1) = ','

CREATE TABLE #pForm
(
sP VARCHAR(50)
)

SET @sP = ',' + @sP + ','

INSERT #pForm
SELECT DISTINCT
SUBSTRING(@sP, Number + 1, CHARINDEX(',', @sP, Number + 1) - Number - 1)
FROM Tally
WHERE Number < DATALENGTH(@sP)
AND SUBSTRING(@sP, Number, 1) = ','

CREATE TABLE #ClassID
(
Class INT
)

SET @ClassID = ',' + @ClassID + ','

INSERT #ClassID
SELECT DISTINCT
SUBSTRING(@ClassID, Number + 1, CHARINDEX(',', @ClassID, Number + 1) - Number - 1)
FROM Tally
WHERE Number < DATALENGTH(@ClassID)
AND SUBSTRING(@ClassID, Number, 1) = ','

SELECT r.ID,
r.sP,
r.ClassID,
r.MoneyValue,
r.NetAmount,
r.Datestarted,
r.DateEnded
FROM Risk AS r
LEFT JOIN #ID

IF @ID IS NULL AND @sP IS NULL AND @ClassID IS Null
SELECT ID,
sP,
ClassId,
MoneyValue,
NetAmount,
Datestarted,
DateEnded
FROM Risk
ELSE
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)


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"
karthik M
karthik M
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13163 Visits: 2588
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
karthik M
karthik M
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13163 Visits: 2588
Shall we do this with UNION ?

karthik
SwePeso
SwePeso
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18585 Visits: 3433
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"
karthik M
karthik M
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13163 Visits: 2588
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
SwePeso
SwePeso
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18585 Visits: 3433
Which is it?
You can't have both.


N 56°04'39.16"
E 12°55'05.25"
karthik M
karthik M
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13163 Visits: 2588
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search