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.
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 2I 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 intASDECLARE @FolderIDs VARCHAR(100)SELECT @FolderIDs = FolderID from hr_EmployeeSavedRegistrantswhere deleted = 0and EmployeeID= 9Please suggest how to do itThanks |
|
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 intASDELETE hFROM hr_Folders hWHERE FolderID = @FolderIDAND EmployeeID = @EmployeeIDUPDATE hr_EmployeeSavedRegistrants SET FolderID = REPLACE('|' + FolderID + '|','|' + CAST(@FolderID AS varchar(10)) + '|','|')WHERE '|' + FolderID + '|' LIKE '%|' + CAST(@FolderID AS varchar(10)) + '|%'DELETE rFROM hr_EmployeeSavedRegistrants rWHERE FolderID = '|'GO[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|