Cacher is the code snippet organizer for pro developers

--- STUFF 
DECLARE @JobCode varchar(20)='J0000009631'

SELECT STUFF((
select distinct ','+ CAST(j.ParentID as varchar(10)) FROM [dbo].[Textile_Dye_Lab_Recipe_Job_T] j  
where  j.JobCode=@JobCode
for xml path('a'), type).value('.','nvarchar(max)'),1,1,'') as RecipeID

--- COALESCE Use as stuff
Select @certificateCodes = COALESCE(@certificateCodes + ',' + certificate_code, certificate_code) 
From production_pur_order_certificate_t_x cer where cer.purchase_order_code='PD011569' and cer.version= 0
 
 ---Output:
@certificateCodes: 00002,00003,00004

----Split then stuff
SELECT STUFF((
select distinct ','+ Value FROM dbo.GetWeekDays() where DayNo IN
(select val FROM split((Select HET.EmpHolyDay  From HRMS_Employee_T as HET 
Where HET.EmpId = 13),','))
for xml path('a'), type).value('.','nvarchar(max)'),1,1,'') as Holiday

--CHARINDEX() searches for a substring within a larger string, 
--and returns the position of the match, 
--or 0 if no match is found

if CHARINDEX('ME',@mainString) > 0
begin
    --do something
end

---- joining with comma
SELECT distinct purOrder.order_code + ', ' AS 'data()' 
FROM DEVDB.dbo.production_pur_order_child_t_x purOrder 
where purOrder.purchase_requisition_code = 'PR015598' FOR XML PATH('')


----Split
select val FROM split('3,4',',')  --- check below for split function

--OR
(Select val from split(@EmpId,','))

--OR
SELECT Value FROM dbo.GetWeekDays() where DayNo IN 
(select val FROM split((Select HET.EmpHolyDay  From HRMS_Employee_T as HET Where HET.EmpId = 13),','))

----Split in Where condition
WHERE SkillChild.[Operation_TypeID] in (select val from split(@OperationTypeIDc,',')) 

SELECT @ReturnIDs = STUFF((
        SELECT ' , ' + CAST(T.[TargetID] as varchar(20)) FROM #TargetT T
        LEFT OUTER JOIN #SalaryT S ON S.TargetID = T.TargetID
        WHERE S.TargetID IS NULL
        FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

		SET @ValidationMsg+=' .Salary is not set for employee ID : '+ @ReturnIDs;	

ALTER FUNCTION [dbo].[split](
    @delimited NVARCHAR(MAX),
    @delimiter NVARCHAR(100)
) RETURNS @t TABLE (id INT IDENTITY(1,1), val NVARCHAR(MAX))
AS
BEGIN
    DECLARE @xml XML
    SET @xml = N'<t>' + REPLACE(@delimited,@delimiter,'</t><t>') + '</t>'
    INSERT INTO @t(val)
    SELECT  r.value('.','varchar(MAX)') as item
    FROM  @xml.nodes('/t') as records(r)
    RETURN
END

 
 DECLARE @TeamStr VARCHAR(MAX)
SELECT  @TeamStr= COALESCE(@TeamStr+',' , '') + convert (varchar(10),tm.teamId)
FROM TeamMember_T tm  where tm.userId = 2
group by tm.teamId  order by tm.teamId

select @TeamStr


--- OUTPUT: 1,28,29,30