IN Operator how to use MySQL

Karthick Ayyam

9y897SQL

how to ssrs report include the IN opertor

SELECT DISTINCT s.time_ref, s.time_date, s.time_hrs, s.time_emp, e.emp_code, e.emp_firstname, i.WBSACTIVITY
FROM site_timesheet s, site_employee_master e, in_wbs_master i
WHERE (time_date BETWEEN ? AND ?) AND (s.time_emp = e.emp_id) AND (s.time_act = i.WBSCDE) AND (emp_code IN (?))
GROUP BY s.time_emp, s.time_ref, s.time_date, s.time_type, i.WBSACTIVITY, s.time_hrs, e.emp_firstname

Error Multivalue Not accept in Emp_code

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Himanshu Gupta9y ago
Accepted answer
Hi Karthick,

You can use datatable in the codebase to store all the selected empcode and pass it into the storedprocdure as parameter through ADO.Net . And then create a TableValuedDataType in SSMS and then use it as parameter in your procedure.
and just use it in WHERE clause.e.g,

See Hihlighted text:
Here SchoolID is TableValuedDataType

ALTER PROCEDURE [dbo].[educator_GetStudent]

@SchoolIDs SchoolID READONLY,
@Gender VARCHAR(50)
AS

SELECT ISNULL(SSE.FlagReportGenerated,0) AS FlagReportGenerated,

SSE.EngageSchoolSafetyClimatePercentile,
FROM dat_Student_Scores_Educator SSE INNER JOIN dat_School S
ON S.SchoolID = SSE.NCESID
WHERE (@Gender = '-- Select Gender --' OR Gender = @Gender)
AND ((SELECT COUNT(SchoolId) FROM @SchoolIDs) = 0 OR S.SchoolID IN (SELECT SchoolId FROM @SchoolIDs))
ORDER BY StudentName
+1
Karthick Ayyam9y ago
Thank you Himanshu jaiswal
0
Karthick Ayyam9y ago
i am using multiselect dropdown fill the emp_code

only select emp_code parameter pass and fetch the data
0
Himanshu Gupta9y ago
You need to pull only one column in result set, e.g,

"WHERE emp_code IN (SELECT e1.emp_id FROM site_employee_master e1)"
0