SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 updating column containing list separated by |
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sparrow37
Posting Yak Master

147 Posts

Posted - 05/01/2013 :  08:56:50  Show Profile  Reply with Quote

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

Edited by - sparrow37 on 05/01/2013 09:22:23

waterduck
Aged Yak Warrior

Malaysia
961 Posts

Posted - 05/01/2013 :  22:51:35  Show Profile  Reply with Quote

declare @hr_Folders TABLE(FolderId int, FolderName varchar(8), EmployeeId int, NumberOfEntries int, IsDeleted int)
insert into @hr_Folders values 
(1, 'Folder 1', 9, 0, 1), 
(2, 'Folder 2', 9, 0, 1),
(3, 'Folder 3', 9, 0, 1)

declare @hr_EmployeeSavedRegistrants TABLE(ID int, EmployeeID int, RegistrantID int, FolderID varchar(10))
insert into @hr_EmployeeSavedRegistrants values
(44, 9, 4, '1|2'),
(45, 9, 5, '1|3'), 
(46, 9, 6, '2')

UPDATE a 
SET a.FolderID = STUFF((SELECT '|' + CAST(FolderId AS VARCHAR) FROM @hr_Folders b WHERE a.EmployeeID = b.EmployeeID AND b.IsDeleted = 0 FOR XML PATH('')), 1, 1, '')
FROM @hr_EmployeeSavedRegistrants a

Edited by - waterduck on 05/01/2013 22:54:09
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 05/02/2013 :  01:31:52  Show Profile  Reply with Quote

CREATE PROC GetResumeCountInfolder
@EmployeeID int,
@FolderID int

AS

DELETE h
FROM hr_Folders h
WHERE FolderID = @FolderID
AND EmployeeID = @EmployeeID

UPDATE hr_EmployeeSavedRegistrants 
SET FolderID = REPLACE('|' + FolderID + '|','|' + CAST(@FolderID AS varchar(10)) + '|','|')
WHERE '|' + FolderID + '|' LIKE '%|' + CAST(@FolderID AS varchar(10)) + '|%'

DELETE r
FROM hr_EmployeeSavedRegistrants r
WHERE FolderID = '|'
GO


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000