I used this code in SQL Server:
CREATE TYPE ExampleType AS TABLE (Number INT)
GO
CREATE FUNCTION GetExampleTableType(@InputNumber INT)
RETURNS ExampleType
AS
BEGIN
DECLARE @OutputTable ExampleType;
INSERT INTO OutputTable
VALUES (@InputNumber);
RETURN(@OutputTable);
END;
GO
But I got an error:
Must declare the scalar variable "@OutputTable"
I have declared @OutputTable but it cannot be a scalar value, it must be a table.
What is wrong?
You are trying to return table variable from scalar valued User defined function. You need to convert the function as given below to store the values into the table valued parameter.
Also, read this StackOverFlow Post, where you cannot return UDT from table valued function.
CREATE TYPE ExampleType AS TABLE (Number int)
GO
CREATE Function GetExampleTableType(@InputNumber int)
RETURNS Table
AS
RETURN
(SELECT @InputNumber AS int);
DECLARE @OutputTable ExampleType
INSERT INTO @OutputTable
SELECT * FROM dbo.GetExampleTableType (1);
Actually you don't need to create a user-defined table type. You can directly give table instead of that.
Query
CREATE Function GetExampleTableType(@InputNumber int)
RETURNS @OutputTable TABLE
(
Number int
)
AS
BEGIN
INSERT INTO @OutputTable VALUES (@InputNumber);
RETURN;
END;
As per my understanding we can use tabletype variables to pass as arguments to functions and stored procedures but not used in multistatemnet table valued functions like you mentioned in the question. But in your example you try to access tabletype variable in returns clause which is syntactically incorrect.
This is what i have as sample code here to use tabletype variables in functions context.. please let me know if any thing i missed to understand your question...
CREATE TYPE TableType
AS TABLE (LocationName VARCHAR(50))
GO
CREATE FUNCTION Example( @TableName TableType READONLY)
RETURNS VARCHAR(50)
AS
BEGIN
DECLARE @name VARCHAR(50)
SELECT @name = LocationName FROM @TableName
RETURN @name
END
DECLARE @myTable TableType
INSERT INTO @myTable(LocationName) VALUES('aaa')
SELECT dbo.Example(@myTable)
Table-valued parameters have the following restrictions:
SQL Server does not maintain statistics on columns of table-valued parameters.
Table-valued parameters must be passed as input READONLY parameters to Transact-SQL routines. You cannot perform DML operations such as UPDATE, DELETE, or INSERT on a table-valued parameter in the body of a routine.
You cannot use a table-valued parameter as target of a SELECT INTO or INSERT EXEC statement. A table-valued parameter can be in the FROM clause of SELECT INTO or in the INSERT EXEC string or stored procedure.
