Using COALESCE to create delimited strings in SQL Server

According to MSDN, COALESCE returns the first non-null expression among its arguments. Returns the data type of expression with the highest data type precedence. At least one of the null values must be a typed NULL.

Using COALESCE to create delimited strings :

We have a table tbl_mst_country

SELECT * FROM tbl_mst_country

Result :

ID  Country
===========
1  UAE
2  EGY
3  KSA

Now we want to generate a string containing all the country id delimited by ";".

DECLARE @delimitedIDs varchar(20)
SELECT @delimitedIDs = COALESCE(@delimitedIDs + '; ', '') + CONVERT(VARCHAR(3),pk_intcountryid)
FROM tbl_mst_country
SELECT @delimitedIDs

Result :

1; 2; 3; 4; 5; 6; 7;

Passing a Comma Delimited String Parameter to a Stored Procedure in SQL Server

When a comma delimited string is passed in a stored procedure as parameter, that cannot be used directly in query unless it is being parsed.
The below function parses the comma delimited string and extract each value,places it in a table and then can be used like a simple table.

User Defined Function :

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[GET_STRING_IDS](@strIDs VARCHAR(8000))
RETURNS @TempTab TABLE
(
stringID VARCHAR(100)
)
AS
BEGIN
IF @strIDs IS NULL OR LEN(LTRIM(RTRIM(@strIDs))) = 0
RETURN

DECLARE
@strTemp VARCHAR(8000),
@intCurrCommaIndex INT,
@strInsertingID VARCHAR(100)
SELECT @strTemp = @strIDs

WHILE LEN(@strTemp) <> 0
BEGIN
SET @intCurrCommaIndex = CHARINDEX(',',@strTemp)
IF @intCurrCommaIndex > 0
BEGIN
SELECT @strInsertingID = SUBSTRING(@strTemp,1,@intCurrCommaIndex - 1)
SELECT @strTemp = SUBSTRING(@strTemp,@intCurrCommaIndex + 1,LEN(@strTemp) - @intCurrCommaIndex)
END
ELSE
BEGIN
SELECT @strInsertingID = @strTemp
SELECT @strTemp = ''
END
INSERT INTO @TempTab(stringID) VALUES(@strInsertingID)
END
RETURN
END


Callign the function from Stored Procedure :

CREATE PROCEDURE getEmpDetails
(
@strEmpIds VARCHAR(8000)=NULL
)
AS
BEGIN
IF(@strStatusIds IS NOT NULL )
BEGIN
SELECT * from tbl_Emp WHERE PK_EmpID IN(SELECT * FROM dbo.GET_STRING_IDS(@strEmpIds))
END

END