GET Multiple Records with Multiple Inputs-Stored Procedure

GET Multiple Records with Multiple Inputs-Stored Procedure 
CREATE TABLE [dbo].[Data](
[Code] [varchar](50) NULL,
[Comments] [varchar](50) NULL
)
-----------------------------------
CREATE PROCEDURE [dbo].[GetData]
@Var1 as varchar(50),
@Var2 as varchar(50),
@Var3 as varchar(50),
@Var4 as varchar(50),
@Var5 as varchar(50)
AS
BEGIN
SELECT * FROM DATA WHERE Code = @Var1 OR Code = @Var2 OR Code = @Var3 OR Code = @Var4 OR Code = @Var5
END
exec GetData 'a','b','c','d','z'
-------------------------------------------------
CREATE PROCEDURE [dbo].[GetData1]
@Countries nvarchar(max)
AS
BEGIN
DECLARE @SQL nvarchar(max)
SET @SQL = 'SELECT * FROM [DATA]
WHERE [Code] IN ('+@Countries+')'
EXEC (@SQL)
END
EXEC [GetData1] '''a'',''b'',''c'',''d'''
---------------------------------------------------
CREATE PROCEDURE [dbo].[GetData2]
@pvcFQNS nvarchar(max)
AS
BEGIN
DECLARE @lvcFQN nvarchar(max)
CREATE TABLE #EmployeeInfo (
FQN CHAR(10),
EMAILID char(30) )
IF RIGHT(@pvcFQNS,1) != ',' SET @pvcFQNS = @pvcFQNS + ','
WHILE Len(@pvcFQNS) > 0
BEGIN
SET @lvcFQN = SUBSTRING(@pvcFQNS, 1, CHARINDEX(',', @pvcFQNS)-1)
INSERT INTO #EmployeeInfo (FQN,EMAILID)
select * FROM Data where Code = @lvcFQN
SET @pvcFQNS = SUBSTRING(@pvcFQNS, CHARINDEX(',', @pvcFQNS)+1, Len(@pvcFQNS))
END
Select EMAILID from #EmployeeInfo
END
EXEC GetData2 'a,b,c'
---------------------------------------------------------------
CREATE PROCEDURE [dbo].[GetData3]
@pvcFQNS nvarchar(max),
@pnoutput nvarchar(max) output
AS
BEGIN
DECLARE @lvcFQN nvarchar(max)
DECLARE @OutPut nvarchar(max)
DECLARE @OutPut2 nvarchar(max)
IF RIGHT(@pvcFQNS,1) != ',' SET @pvcFQNS = @pvcFQNS + ','
WHILE Len(@pvcFQNS) > 0
BEGIN
SET @lvcFQN = SUBSTRING(@pvcFQNS, 1, CHARINDEX(',', @pvcFQNS)-1)
if (select Count(Code) FROM Data where Code = @lvcFQN)>0
BEGIN
Set @OutPut2 = (select Code FROM Data where Code = @lvcFQN )
if @OutPut ''
Set @OutPut =@OutPut +',' + @OutPut2
else
Set @OutPut =@OutPut2
END
SET @pvcFQNS = SUBSTRING(@pvcFQNS, CHARINDEX(',', @pvcFQNS)+1, Len(@pvcFQNS))
SET @OutPut2=''
END
set @pnoutput = @OutPut
declare @pnoutput nvarchar(max)
EXEC [GetData3] 'a,b,c',@pnoutput output
select @pnoutput
-----------------------
CREATE PROCEDURE [dbo].[GetUserEmailID]
@pvcUserIDs NVARCHAR(MAX),
@pvcUserEmailIDs NVARCHAR(MAX) OUTPUT
AS
BEGIN
-- SET NO COUNT ON ADDED TO PREVENT EXTRA RESULT SETS FROM
-- INTERFERING WITH SELECT STATEMENT
SET NOCOUNT ON
DECLARE @lvcFQN NVARCHAR(MAX)
DECLARE @FinalFQN NVARCHAR(MAX)
DECLARE @TempFQN NVARCHAR(MAX)
IF RIGHT(@pvcUserIDs,1) != ';'
BEGIN
SET @pvcUserIDs = @pvcUserIDs + ';'
WHILE LEN(@pvcUserIDs) > 0
BEGIN TRY
SET @lvcFQN = SUBSTRING(@pvcUserIDs, 1, CHARINDEX(';', @pvcUserIDs)-1)
IF (SELECT COUNT(Email) FROM dbo.TblEmployeeInfo WHERE FQN = @lvcFQN)>0
BEGIN
SET @TempFQN = (SELECT Email FROM dbo.TblEmployeeInfo WHERE FQN = @lvcFQN)
IF @FinalFQN ''
SET @FinalFQN =@FinalFQN +';' + @TempFQN
ELSE
SET @FinalFQN =@TempFQN
END
SET @pvcUserIDs = SUBSTRING(@pvcUserIDs, CHARINDEX(';', @pvcUserIDs)+1, Len(@pvcUserIDs))
SET @TempFQN=''
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS 'ERROR NUMBER', ERROR_MESSAGE() AS 'ERROR MESSAGE'
END CATCH
END
ELSE
BEGIN
WHILE LEN(@pvcUserIDs) > 0
BEGIN TRY
SET @lvcFQN = SUBSTRING(@pvcUserIDs, 1, CHARINDEX(';', @pvcUserIDs)-1)
IF (SELECT COUNT(Email) FROM dbo.TblEmployeeInfo WHERE FQN = @lvcFQN)>0
BEGIN
SET @TempFQN = (SELECT Email FROM dbo.TblEmployeeInfo WHERE FQN = @lvcFQN)
IF @FinalFQN ''
SET @FinalFQN =@FinalFQN +';' + @TempFQN
ELSE
SET @FinalFQN =@TempFQN
END
SET @pvcUserIDs = SUBSTRING(@pvcUserIDs, CHARINDEX(';', @pvcUserIDs)+1, Len(@pvcUserIDs))
SET @TempFQN=''
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS 'ERROR NUMBER', ERROR_MESSAGE() AS 'ERROR MESSAGE'
END CATCH
END
SET @pvcUserEmailIDs = @FinalFQN
-- RE SETTING NO COUNT
SET NOCOUNT OFF
END
--declare @a nvarchar(MAX)
--exec [GetUserEmailID] 'a;b', @a output
--print @a