praveenkrishp praveenkrishp - 3 months ago 15
SQL Question

SQL custom function calling an other function, shows error

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".

Answer

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