How to Execute Sql Function that returns a Table EXECUTING Table-valued functions in Sql Server

Must declare the scalar variable was the error initially faced while executing Tabled Value function like Normal Function Execution, Hence writing this post.

As we all know that Scalar-valued functions return a single value whereas Table-valued functions returns tabular result sets that is it returns a table as output.

Summarizing - User-defined functions that return a table are referred to as table-valued functions.

Example Table Valued Function – Splits Values based on a particular Character

CREATE FUNCTION [dbo].[fun_SplitCommas](@String varchar(8000))     returns @temptable TABLE (items varchar(8000))     AS     BEGIN         declare @idx int         declare @partValue varchar(8000)           select @idx = 1         IF len(@String)<1 or @String is null      BEGIN             RETURN         END     WHILE @idx!= 0         BEGIN         --Searches an expression for another expression and returns its starting position if found.         SET @idx = charindex(',',@String)            --LEFT Func Returns the left part of a string with specified number of characters.         IF @idx!=0                 SET @partValue = LEFT(@String,@idx - 1)              ELSE                 SET @partValue = @String                     IF(len(@partValue)>0)             insert into @temptable(Items) values(@partValue)                 --GOING TO THE NEXT ITERATION         SET @String = right(@String,len(@String) - @idx)             IF len(@String) = 0 break         END       RETURN     END

Initial Tried approach – Wrong Approach

Declare @temptable Table (   items varchar(8000) ); Declare @String NVARCHAR(2000); -- Execute the function while passing a value to the @status parameter EXEC @temptable = dbo.fun_SplitCommas @String  = '512,121';

Output – Failed with below error – Must declare the scalar variable "@temptable".

Correct Approach – 1

Select *  From fun_SplitCommas('123,232') Or Select items  From fun_SplitCommas('123,232')

Correct  Approach – 2

Declare @temptable1 Table (   items varchar(8000) ); INSERT INTO @temptable1 Select *  From fun_SplitCommas('123,232') Select * From @temptable1

Second Approach has the advantage that I Could store the result in a Temporary Table as well.

USES OF TABLE VALUED USER FUNCTION

• A table-valued user-defined function can be used where table or view expressions are allowed in Transact-SQL queries.
• A table-valued user-defined function can also replace stored procedures that return a single result set.