updating column containing list separated by

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

updating column containing list separated by |

Author

Topic

sparrow37
Posting Yak Master

148 Posts

Posted - 2013-05-01 : 08:56:50

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

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-05-01 : 22:51:35

[code]
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
[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-02 : 01:31:52

[code]
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
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources