SQL Server: Pass Multiple values as parameter dynamically

Here is an example to show how multiple values can be passed as parameter which can be used in stored procedure.

CREATE PROC [dbo].[DynamicSQL_PassMultiVal](@EmpName varchar(500))
AS
BEGIN

DECLARE @SQLStatement varchar(max)
SET @EmpName = Replace(@EmpName,',',''',''')
SET @SQLStatement='select * from Employee where EmployeeName in (' + QuoteName(@EmpName) + ')'

EXEC (@SQLStatement)

END

Without using QUOTENAME() we can write the query as

ALTER PROC [dbo].[DynamicSQL_PassMultiVal](@EmpName varchar(500))
AS
BEGIN

DECLARE @SQLStatement varchar(max)
SET @EmpName = Replace(@EmpName,',',''',''')
SET @SQLStatement='select * from Employee where EmployeeName in (' + '''' + @EmpName + ''')'

EXEC (@SQLStatement)

END

Usage

[DynamicSQL_PassMultiVal] 'Bheem,Krishna'