IN Operator how to use MySQL

Question

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

Himanshu Jaiswal · 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.FlagReportGenerated0) 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

Karthick Ayyam · Answer

Thank you Himanshu jaiswal

Karthick Ayyam · Answer

i am using multiselect dropdown fill the emp_code only select emp_code parameter pass and fetch the data

Himanshu Jaiswal · Answer

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 )