Hi,
Check this ...
As per your required O/P
CREATE PROC PR_GET_VAL
(
@PA_SERACH_VAL VARCHAR(200)
)
AS
BEGIN
CREATE TABLE #TMP_SEARCH_VAL
(
COL1 VARCHAR(10)
)
INSERT INTO #TMP_SEARCH_VAL
SELECT * from split(@PA_SERACH_VAL,',')
CREATE TABLE #TMP_MAIN_TBL
(
ID INT,
LOCID VARCHAR(10)
)
DECLARE @CNT INT
DECLARE @MAXCNT INT
SELECT @MAXCNT=MAX(id) FROM test_Main_TBL
SET @CNT=1
DECLARE @LOC_VALS VARCHAR(200)
PRINT(@MAXCNT)
PRINT(@CNT)
WHILE @CNT <= @MAXCNT
BEGIN
SELECT @LOC_VALS=locationId from test_Main_TBL WHERE id=@CNT
INSERT INTO #TMP_MAIN_TBL
SELECT @CNT,* FROM SPLIT(@LOC_VALS,',')
PRINT(@CNT)
SET @CNT= @CNT + 1
END
SELECT DISTINCT ID FROm #TMP_MAIN_TBL WHERE LOCID IN (SELECT COL1 FROM #TMP_SEARCH_VAL)
END
Caution : Execution May get slow as Data increases.
Hope this will help you.
Cheers