Not able to fetch Data from Stored Procedure IN Clause

Question

I am trying to execute a store Procedure but not able to fetch the data from it. Below is the Store Procedure.

USE test
GO
CREATE PROCEDURE SELECT_IDs @idList nvarchar(1750)
AS
BEGIN TRY
SELECT DISTINCT child FROM example WHERE parent IN (@idList)
UNION
SELECT DISTINCT parent FROM example WHERE child IN (@idList)
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber;
END CATCH
GO

EXEC SELECT_IDs @idList = ['100','101'];

After executing the above procedure it is giving me no data where as if i run the same select query it is giving me data.

SELECT DISTINCT child FROM example WHERE parent IN ('100','101')

After reading the comments i tried the below code but not working.

/*
Splits string into parts delimitered with specified character.
*/
CREATE FUNCTION [dbo].[SDF_SplitString]
(
@sString nvarchar(2048),
@cDelimiter nchar(1)
)
RETURNS @tParts TABLE ( part nvarchar(2048) )
AS
BEGIN
if @sString is null return
declare @iStart int,
        @iPos int
if substring( @sString, 1, 1 ) = @cDelimiter 
begin
    set @iStart = 2
    insert into @tParts
    values( null )
end
else 
    set @iStart = 1
while 1=1
begin
    set @iPos = charindex( @cDelimiter, @sString, @iStart )
    if @iPos = 0
        set @iPos = len( @sString )+1
    if @iPos - @iStart > 0          
        insert into @tParts
        values  ( substring( @sString, @iStart, @iPos-@iStart ))
    else
        insert into @tParts
        values( null )
    set @iStart = @iPos+1
    if @iStart > len( @sString ) 
        break
end
RETURN
END

And Modified the stored Procedure to

USE test
GO
CREATE PROCEDURE SELECT_IDs @idList nVarchar(2048)
AS
BEGIN TRY
SELECT DISTINCT child FROM example WHERE parent IN ([dbo].[SDF_SplitString] 
('@idList',',')) or child IN ([dbo].[SDF_SplitString]('@idList',','))
UNION
SELECT DISTINCT parent FROM example WHERE parent IN ([dbo].[SDF_SplitString] 
(@idList,',')) or child IN ([dbo].[SDF_SplitString](@idList,','))
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber;
END CATCH
GO

But getting the issue : Cannot find either column "dbo" or the user-defined function or aggregate "dbo.SDF_SplitString", or the name is ambiguous.

your code will not work. You have to use dynamic sql for the parameters you pass. — Venkataraman R, May 28 '18 at 09:51
you can also used a table valued function to convert the comma separated list as a table variable and use that in the join clause with example. — Venkataraman R, May 28 '18 at 09:53

Suresh Prajapati · Accepted Answer · 2018-05-31T06:26:26.703

1

If you are using SQL Server 2016 and above, then try using STRING_SPLIT

USE test GO
    CREATE PROCEDURE SELECT_IDs @idList nvarchar(1750) AS BEGIN TRY
    SELECT DISTINCT child
    FROM example
    WHERE parent IN
        (SELECT value
         FROM STRING_SPLIT(@idList, ','))
    UNION
    SELECT DISTINCT parent
    FROM example
    WHERE child IN
        (SELECT value
         FROM STRING_SPLIT(@idList, ',')) END TRY BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber; END CATCH 
GO

EXEC SELECT_IDs @idList = '100,101';

UPDATE 1.0 - Integrating function for split

Modify your STORED PROCEDUREas mentioned below, It seems you missed SELECT clause for the function call.

USE test GO
CREATE PROCEDURE SELECT_IDs @idList nVarchar(2048) AS BEGIN TRY
SELECT DISTINCT child
FROM example
WHERE parent IN (SELECT * FROM SDF_SplitString(@idList, ','))
  OR child IN (SELECT * FROM SDF_SplitString(@idList, ','))
UNION
SELECT DISTINCT parent
FROM example
WHERE parent IN (SELECT * FROM SDF_SplitString(@idList, ','))
  OR child IN (SELECT * FROM SDF_SplitString(@idList, ',')) END TRY BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber; END CATCH GO

edited May 31 '18 at 06:26

answered May 28 '18 at 09:57

Suresh Prajapati

3,991
5
26
38

Invalid object name 'STRING_SPLIT'. – Pawan Kumar May 28 '18 at 10:05
I guess you are using older version. This is available only and after SQL Server 2016. Refer https://sqlperformance.com/2016/03/sql-server-2016/string-split – Suresh Prajapati May 28 '18 at 10:11
So any idea how to achieve it without STRING_SPLIT – Pawan Kumar May 28 '18 at 10:18
1

You have to create a custom function and use that. Refer - https://stackoverflow.com/questions/697519/split-function-equivalent-in-t-sql?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa – Suresh Prajapati May 28 '18 at 10:21
Not able to do that...Below is the code which i tried – Pawan Kumar May 29 '18 at 07:33
@PawanKumar I have updated the answer with function call – Suresh Prajapati May 31 '18 at 07:31

Not able to fetch Data from Stored Procedure IN Clause

1 Answers1