Link to home
Start Free TrialLog in
Avatar of Mehram
MehramFlag for Pakistan

asked on

How to run comma separated values in loop

Dear Experts,
Need your help
The below mentioned query need to run three times since @FeeType is carrying three string (1,4,5)
Please help me


Declare @FeeType Varchar(100)
Set @FeeType='1,4,5'


select @FeeAmount = (select FeeAmount From ClassesFee Where ClassId = @ClassId and FeeTypeId = @FeeTypeId and FiscalYearID=@FiscalYearID and CampusId=@CampusId)

Insert Into S(StudentName,@FeeAmount)
Select StudentName,@FeeAmount from Students
Avatar of Habib Pourfard
Habib Pourfard
Flag of New Zealand image

If you want to insert FeeAmount for all of students then you could write:
Declare @FeeType Varchar(100)
Set @FeeType='1,4,5'

Insert Into S (StudentName, FeeAmount)
SELECT S.StudentName, CF.FeeAmount FROM dbo.Students S CROSS JOIN 
(SELECT   FeeAmount
    FROM     ClassesFee
    WHERE    ClassId = @ClassId
            AND @FeeTypeId LIKE '%' + CAST(FeeTypeId AS VARCHAR(8)) + '%'
            AND FiscalYearID = @FiscalYearID
            AND CampusId = @CampusId) CF

Open in new window

Avatar of Mehram

ASKER

Sir,
I have select only three types of fee and need to create fee against those three type of all student.

The appended query is generating fee against all fee type what ever in classesFee.
need to review query to select only provided fee types (@FeeType='1,4,5')


Declare @FeeType Varchar(100)
Set @FeeType='1,4,5'

Create Table s (StudentName Varchar(100), FeeAmount int)
Insert Into S (StudentName, FeeAmount)
SELECT S.StudentName, CF.FeeAmount FROM dbo.Students S CROSS JOIN
(SELECT   FeeAmount
    FROM     ClassesFee
    WHERE    ClassId = '1'
            AND @FeeType LIKE '%' + CAST(FeeTypeId AS VARCHAR(8)) + '%'
            AND FiscalYearID = '1'
            AND CampusId = '1') CF
Select * from s
Drop Table s
Are you sure? the following condition should restrict the result to those with FeeTypeID of 1,4,5:
WHERE @FeeType LIKE '%' + CAST(FeeTypeId AS VARCHAR(8)) + '%'

Open in new window

then the cross join will create the desired result, assume we have two students (A and B) and three FeeAmounts (10, 40, 50):
A 10
A 40
A 50
B 10
B 40
B 50
Avatar of Mehram

ASKER

Sir student in table is 614 result of above query generating 3927 rows, while its need to generate 1842 rows. Since i have selected three types of fee (614*3)
strange result, 3927 / 614 = 6.4 (!!!)
could you please run the following query and tell me the output:
Declare @FeeType Varchar(100)
Set @FeeType='1,4,5'

SELECT   FeeAmount
FROM     ClassesFee
WHERE @FeeType LIKE '%' + CAST(FeeTypeId AS VARCHAR(8)) + '%'

Open in new window


what about this one:
SELECT   FeeAmount
FROM     ClassesFee
WHERE FeeTypeId IN (1,4,5)

Open in new window

Avatar of Mehram

ASKER

SELECT   FeeAmount
FROM     ClassesFee
WHERE FeeTypeId IN (1,4,5) and ClassId='1'

result Three rows
User generated image
Avatar of Mehram

ASKER

Result of this query is also same as above three rows

Declare @FeeType Varchar(100)
Set @FeeType='1,4,5'

SELECT   FeeAmount
FROM     ClassesFee
WHERE @FeeType LIKE '%' + CAST(FeeTypeId AS VARCHAR(8)) + '%'
and ClassId='1'
ASKER CERTIFIED SOLUTION
Avatar of Habib Pourfard
Habib Pourfard
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial