updating column containing list separated by | using sql

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

Answers


Here is a solution given your table structure, however, I would agree with Mahmoud -- you really should normalize your tables. Create a new table called hr_EmployeeFolders with EmployeeId and FolderId as your two fields. Then you can store a 1-n number of employees and folders. Much easier to maintain this way.

However, given your current scenario, something like this should work:

create proc updatefolder 
  @FolderId int, @EmployeeId int
as
begin
  update hr_Folders 
  set isdeleted = 1
  where folderId = @folderId and employeeId = @employeeId

  update hr_EmployeeSavedRegistrants 
  set FolderId = replace(replace(folderId, @FolderId, ''),'||','|')
  where employeeId = @employeeId

  update hr_EmployeeSavedRegistrants 
  set FolderId =
    case 
      when left(FolderId,1) = '|' then right(folderId, len(folderId) - 1)
      when right(FolderId,1) = '|' then left(folderId, len(folderId) - 1)
      else FolderId
    end
  where employeeId = @employeeId
end

SQL Fiddle Demo

There are some other options splitting the list and recreating, but this is the quickest I could think of. The first update statement updates the deleted column in the folders table. The second update statement removes the folder id from the EmployeeSavedRegistrants table, along with any potential instances of double pipes. The final update statement removes any beginning or trailing pipes.


Need Your Help

Extract date from a long string

r date

I have a data frame where the date format is as follows:

Could not load file or assembly

.net linqpad

I'm not able to execute code in another application domain in Linqpad:

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.