function is not working 'in operator' method in stored procedure

[asok]

I am trying to execute below sp but i am unable to get the result.

reasion is i am using function which is return string values like ('ab','df','dfff').when i use this function in operator it is not returning any value ..anybody help me why it is not returning ?.

function name ([dbo].fnParseSplitString(@stQueueName)))
 
 
ALTER PROCEDURE [Strategies_uspGetNoCreditBureauReview]
    -- Add the parameters for the stored procedure here
    @StrategiesId int
 
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
 
    DECLARE @stQueueName varchar(50) 
 
    SET NOCOUNT ON;
    if @StrategiesId is not null 
    begin
    -- select statements for procedure here
    select @stQueueName = (SELECT StrategiesQueueandWamName from [dbo].[Strategies] where StrategiesId = @StrategiesId)
 
        select qc.QueueId,qc.QueueName,wc.WamName,lc.LocationName
        from Channelsmapping cm
        join [dbo].[QueueChannels]  qc on 
        cm.QueueId = qc.QueueId
        join LocationChannels lc on
        lc.LocationId = cm.LocationId
        join [dbo].[WamChannels] wc on
        wc.WamId = cm.WamId
        where qc.Queueid in (select queueid from [dbo].[QueueChannels] where queuename in ([dbo].fnParseSplitString(@stQueueName)))
        group by qc.QueueId,qc.QueueName,wc.wamname,lc.locationname
        order by qc.QueueId,qc.QueueName,wc.wamname,lc.locationname asc
    End
END

[Rabbit]

There's not enough information to answer this question. We need to know what value @stQueueName holds. And we need to know the code for the function fnParseSplitString because that is not a native function in SQL Server. Someone on your side created that.

[asok]

I will pass the value to function like 'abc,defd,ghjk' and function will return the value like 'abc','defd','ghjk' so that i can use 'IN' Operate to get the values from database.

Here is the function which return the values

ALTER FUNCTION [dbo].[fnParseSplitString] 
( 
@InputString varchar(255) 
) 
RETURNS varchar(255) 
AS 
BEGIN 
    DECLARE @ParsedInputString varchar(255) 
    DECLARE @ExcludedCourse varchar(10) 
    DECLARE @Pos int 
 
    WHILE LEN(@InputString) > 0 
    BEGIN 
        SET @Pos = CHARINDEX(',',@InputString) 
 
        IF @Pos = 0 
            BEGIN 
                SET @ParsedInputString = ISNULL(@ParsedInputString,'') + '''' + LTRIM(@InputString) + '''' 
            END 
        ELSE 
            BEGIN 
                SET @ExcludedCourse = '''' + SUBSTRING(@InputString, 1, @Pos - 1) + '''' 
                SET    @ParsedInputString = @ExcludedCourse + ',' + ISNULL(@ParsedInputString,'') 
            END 
 
        IF @Pos = 0 
            BEGIN 
                SET @InputString = '' 
            END 
        ELSE 
            BEGIN 
                SET @InputString = LTRIM(SUBSTRING(@InputString, @Pos + 1, LEN(@InputString) - @Pos)) 
            END 
    END 
 
RETURN @ParsedInputString 

it is working when I manually enter the values like

select queueid from queue where queuename in ('abc','defd','ghjk')

it is not working when i use function in 'IN' operator like

select queueid from queue where queuename in ([dbo].fnParseSplitString(@stQueueName)))

function return Varaiable @stQueueName will have the data like ('abc','defd','ghjk').

Please help me why it is not returning the values while using function in 'IN' Operator.

[Rabbit]

Please use code tags when posting code or formatted data.

The IN operator expects one or more values separated by commas. What you gave it is one string. The string just happened to contain commas. It does not see it as separate values.

In essence what you have done is pass this into the WHERE clause:

fieldName IN ('''a'',''b''')

But what it's really expecting is this:

fieldName IN ('a','b')

The difference between the first and second is that the first is only one string, the second is two strings. You have only passed one string because that's what your function returns. You need to modify your function to return multiple strings in the form of a table.