Mehram
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
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
Insert Into S(StudentName,@FeeAmount)
Select StudentName,@FeeAmount from Students
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
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:
A 10
A 40
A 50
B 10
B 40
B 50
WHERE @FeeType LIKE '%' + CAST(FeeTypeId AS VARCHAR(8)) + '%'
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
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:
what about this one:
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)) + '%'
what about this one:
SELECT FeeAmount
FROM ClassesFee
WHERE FeeTypeId IN (1,4,5)
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'
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Open in new window