sahmed4 wrote:
if i have @Roles values="1,3,5,7,8," that will insert in as 1 3 5 7 8 individually in all the newly created rows in rights table but while updating how do handle this
Have a look at this example:
DECLARE @sRoles NVARCHAR(100)
DECLARE @roles TABLE([Roles] NVARCHAR(100))
SET @sRoles = '1,3,5,7,8,'
INSERT INTO @roles ([Roles])
VALUES (@sRoles)
SELECT *
FROM @roles
;WITH SplitedString AS
(
SELECT LEFT([Roles],CHARINDEX(',',[Roles])-1) AS [Role], RIGHT([Roles],LEN([Roles])-CHARINDEX(',',[Roles])) AS Remainder
FROM @roles
WHERE [Roles] IS NOT NULL AND CHARINDEX(',',[Roles])>0
UNION ALL
SELECT LEFT(Remainder,CHARINDEX(',',Remainder)-1), RIGHT(Remainder,LEN(Remainder)-CHARINDEX(',',Remainder))
FROM SplitedString
WHERE Remainder IS NOT NULL AND CHARINDEX(',',Remainder)>0
UNION ALL
SELECT Remainder, NULL
FROM SplitedString
WHERE Remainder IS NOT NULL AND CHARINDEX(',',Remainder)=0
)
SELECT *
FROM SplitedString
WHERE [Role]!=''
ORDER BY [Role]
Result:
Role Remainder
1 3,5,7,8,
3 5,7,8,
5 7,8,
7 8,
8
To update your table, you shold use something like this:
INSERT INTO TableName (Column1)
SELECT *
FROM SplitedString
WHERE [Role]!=''
ORDER BY [Role]