Use Where In clause with Table-Valued User-Defined Function in Sql Server

Question

I have a Table-Valued User-Defined Functions named "GetRelatedUsersIds", which in turn return all sub users (including itself) for any particular user if any.

And I have a scenario where I want this function to be used in where in clause as given below:-

Create procedure [ABC]
(
@AddedBy Int,
@LoggedUser int=0
)
as
SELECT * FROM MyTable 
where AddedBy in (case when @AddedBy =0 then (select UserId from dbo.GetRelatedUsersIds(@LoggedUser)) else @AddedBy end)

Here I looking for variable @AddedBy if its 0 then I will go for variable @LoggedUser.

CREATE function [dbo].[GetRelatedUsersIds]
(
@ccManagerId INT
) 
returns @tableUsersId table(UserId int)
AS
.... code

But as UDF returns a table so I am getting an error of "Subquery returned more than 1 value".

How to solve this issue, TIA.

That isn't how a case expression works. It is designed to return a single value, not control logic like you are trying to do here. — Sean Lange
– Sean Lange, Commented Mar 20, 2017 at 13:33
Well if the function returns more than 1 row you are going to get this error because the subquery returned more than 1 row. — Sean Lange
– Sean Lange, Commented Mar 20, 2017 at 13:40

Sean Lange · Accepted Answer · 2017-03-20 13:44:20Z

1

Pretty sure you need something like this. But don't use select *. Instead you need to be explicit and use the column names you want.

SELECT * 
FROM MyTable 
where AddedBy in 
(
    select UserId 
    from dbo.GetRelatedUsersIds(@LoggedUser) 
    where @AddedBy = 0
)
OR 
(
    @AddedBy <> 0
    AND
    AddedBy = @AddedBy
)

answered Mar 20, 2017 at 13:44

Sean Lange

33.7k3 gold badges28 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Use Where In clause with Table-Valued User-Defined Function in Sql Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related