It is working...
USE [DBNAME]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[sp2_loop]
@param1 varchar(1000)='xxx~yyy'
AS
SET NOCOUNT ON
DECLARE @Prm_id varchar(10), @Pos int
SET @param1 = LTRIM(RTRIM(@param1)) + '~'
SET @Pos = CHARINDEX('~', @param1, 1)
IF REPLACE(@param1, '~', '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @Prm_id = LTRIM(RTRIM(LEFT(@param1, @Pos - 1)))
IF @prm_id <> ''
IF(EXISTS(select 1 from Table_2 where substring(Test1,1,3) = @prm_id))
BEGIN
select * from Table_2 where substring(Test1,1,3) = @prm_id
END
SET @param1 = RIGHT(@param1, LEN(@param1) - @Pos)
SET @Pos = CHARINDEX('~', @param1, 1)
END
END