I have a table named hr_Folders with following info:
FolderId FolderName EmployeeId NumberOfEntries IsDeleted
1 Folder 1 9 1 0
2 Folder 2 9 1 0
3 Folder 3 9 1 0
and there is another table hr_EmployeeSavedRegistrants where folderids are saved as | separated list like this:
ID EmployeeID RegistrantID FolderID
44 9 4 1|2
45 9 5 1|3
46 9 6 2
I want to create a stored procedure where when i pass folderid and employeeid, it deletes that folder from hr_folder and then update the hr_EmployeeSavedRegistrants table's FolderId column accordingly ( remove the folderid from folderid column list and if there is one folderid in hr_EmployeeSavedRegistrants, then delete that row like row 3 in hr_EmployeeSavedRegistrants)
so far written code is:
CREATE PROC GetResumeCountInfolder
@Folder int
AS
DECLARE @FolderIDs VARCHAR(100)
SELECT @FolderIDs = FolderID from hr_EmployeeSavedRegistrants
where deleted = 0
and EmployeeID= 9
Please suggest how to do it
Thanks