I am trying to put outer apply on the table varible but I am getting error like below
Cannot call methods on table.
I have Split function which split the string to certain length
CREATE FUNCTION Split(@String varchar(MAX), @SplitLength int)
RETURNS @Result TABLE (Splited varchar(MAX))
AS
BEGIN
Declare @Cnt int
Set @Cnt = FLOOR((len(@String)/@SplitLength));
While @Cnt!=0
Begin
SET @Cnt=@Cnt-1;
While len(@String)>@SplitLength
Begin
INSERT INTO @Result VALUES (SUBSTRING(@String,1,@SplitLength))
SET @String=SUBSTRING(@String,@SplitLength+1,len(@String)-@SplitLength)
End
End
RETURN
END
which I join with the table variable which contain column which have string to be splited
DECLARE @LeftSuper TABLE
(
KeyTerm VARCHAR(MAX),
Data VARCHAR(MAX) ,
)
Query is as following Which generates error (Cannot call methods on table )
select KeyTerm ,D.Splited from @LeftSuper
outer apply [Split](@LeftSuper.Data,300) as D
Note: code works fine with Real Table in db.
Introduce an alias for the table variable and use that in the expression:
select KeyTerm ,D.Splited from @LeftSuper ls
outer apply [Split](ls.Data,300) as D
This is actually fairly common - because tables may appear more than once in a query, each time that @LeftSuper
is encountered in the query, it's treated as a new reference to the table - not as the reference that has already been added - which the alias allows you to reference.