SQL custom function calling an other function, shows error

Question

I have a table Disciplines

DisciplineID |Discipline_Name
1            |  Aquatics
2            |  Archery
3            |  Athletics

I ave a function that takes a string like ("Aquatics, Archery") and returns a table with entries Aquatics Archery (Basically splits string) the function for that is as below

CREATE FUNCTION SplitString
(    
 @Input NVARCHAR(MAX)
 )
RETURNS @Output TABLE (
  Item NVARCHAR(1000)
)
AS
BEGIN
  DECLARE @StartIndex INT, @EndIndex INT, @Character CHAR(1)
  SET @StartIndex = 1
  SET @Character =','
  IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character
  BEGIN
        SET @Input = @Input + @Character
  END
  WHILE CHARINDEX(@Character, @Input) > 0
  BEGIN
        SET @EndIndex = CHARINDEX(@Character, @Input)
        INSERT INTO @Output(Item)
        SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1)
        SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))
  END
  RETURN
END
GO

Also an other function:

create function getdisID
(
 @Inp varchar(1000)
)
RETURNS @Output TABLE (
  Itemid int
   )
as
begin

SELECT DisciplineID
FROM Disciplines
inner JOIN 
dbo.SplitString(@Inp) as temp
ON Disciplines.Discipline_Name=temp.Item;
end

The second function upon execution produces an error "Select statements included within a function cannot return data to a client".

Possible duplicate of [INNER JOIN with Table-Valued Function not working](http://stackoverflow.com/questions/23402316/inner-join-with-table-valued-function-not-working) — Ricardo Pontual, Aug 16 '16 at 11:06
You can not use select statement directly in function to return data. You need to insert selected data into output table parameter. — D Mayuri, Aug 16 '16 at 11:08

score 6 · Answer 1 · answered Aug 16 '16 at 11:07

6

Does this help

create function getdisID (@Inp varchar(1000))

Returns Table 
As
Return (
    SELECT DisciplineID
    FROM Disciplines 
    inner JOIN dbo.SplitString(@Inp) as temp
    ON Disciplines.Discipline_Name=temp.Item;
)

answered Aug 16 '16 at 11:07

John Cappelletti

79,615
7
44
66

score 2 · Answer 2 · answered Aug 16 '16 at 11:12

2

create function getdisID
(
   @Inp varchar(1000)
)
RETURNS @Output TABLE (
  Itemid int
)
as
begin

INSERT INTO @Output(Itemid)
SELECT DisciplineID
FROM Disciplines
inner JOIN 
dbo.SplitString(@Inp) as temp
ON Disciplines.Discipline_Name=temp.Item;

end

You need to either use stored procedure to return result or you need to insert data in output table parameter

answered Aug 16 '16 at 11:12

D Mayuri

456
2
6

I think you might have forgot the return statement. But thanks it worked.. – praveenkrishp Aug 16 '16 at 12:02

score 1 · Accepted Answer · edited Aug 16 '16 at 12:13

1

The script for second function should be like this:

create function getdisID
(
 @Inp varchar(1000)
)
RETURNS @Output TABLE (
  Itemid int
   )
as
begin

insert into @Output
select DisciplineID
FROM Disciplines
inner JOIN 
dbo.SplitString(@Inp) as temp
ON Disciplines.Discipline_Name=temp.Item;
return
end

edited Aug 16 '16 at 12:13

John Cappelletti

79,615
7
44
66

answered Aug 16 '16 at 11:27

Tarek Abo ELkheir

1,311
10
13

SQL custom function calling an other function, shows error

3 Answers3