SQL Server - updating column containing list separated by | using sql

Asked By asif hameed on 01-May-13 08:56 AM




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

Robbe Morris replied to asif hameed on 01-May-13 05:12 PM