SQL Server Stuff function examples

Stuff function Example

declare @List1  table
(
RateNoticeId INT,
InHouse VARCHAR(100),
StateAccountID VARCHAR(100)
)
Insert into  @List1 values (10,'W070','2323')
Insert into  @List1 values (20,'W070','6552323')
Insert into  @List1 values (90,'W070','342323')
Insert into  @List1 values (70,'S070','92323')
Insert into  @List1 values (90,'S070','6552323')
Insert into  @List1 values (30,'S070','342323')
select * from @List1


select DISTINCT stuff(
        (
            Select DISTINCT ','+ CAST(ST1.RateNoticeId as varchar(100))
            From @List1 ST1
            Where ST1.InHouse = ST2.InHouse --and ST1.email=ST2.email
            For XML PATH ('')
        ), 1,1,'') [RateNoticeId],stuff(
        (
            Select DISTINCT ','+ CAST(ST1.StateAccountID as varchar(100))
            From @List1 ST1
            Where ST1.InHouse = ST2.InHouse --and ST1.email=ST2.email
            For XML PATH ('')
        ), 1,1,'') [StateAccountID],ST2.InHouse

from @List1 ST2

Update the table values and Retrieve the updated values by using the magic table values

UPDATE Table1
SET NotificationStatusId = @inProgressId
OUTPUT INSERTED.Id,INSERTED.Email,INSERTED.NotificationStatusId,INSERTED.Dlm,
INSERTED.RetryCount,INSERTED.YearQtr,INSERTED.Inhouse,INSERTED.ReplyToMail,INSERTED.employerCode , INSERTED.StateAccountsID
WHERE NotificationStatusId = @newId OR  (NotificationStatusId = @errorId AND  ISNULL(RetryCount,0) <= @RetryCount)

Passing Comma Separated Values to Data Base using XML

DECLARE @strString VARCHAR(100);
SET @strString ='UID1001,UID1002,UID1003';

DECLARE @x XML;
SELECT @x = CAST('<A>'+ REPLACE(@strString,',','</A><A>')+ '</A>' AS XML);
SELECT @x; -- How the XML looks like


SELECT * from UserTableName u
       join
              (SELECT t.value('.', 'nvarchar(8)') AS user_id
                      FROM @x.nodes('/A') AS x(t)) Ids on u.USER_NAME=Ids.user_id

SELECT b.value('.', 'nvarchar(8)') AS user_id
                      FROM @x.nodes('/A') AS test(b)

It might be faster if you pass the XML directly as a parameter instead of comma separated values.