CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2014
Alex Zhukovskiy
Alex Zhukovskiy

Reputation: 10025

Return a constant table from Inline-valued table

Is it possible to return a constant table from an IVT? Now i'm using a regular table, and function is just returning data from this table with several additional operations.

So i want to return a table

Value
-----
0,265433
0,0629412
0,671626

I tried something like this:

ALTER FUNCTION [dbo].[GetEigenvector] 
(   

)
RETURNS TABLE 
AS
RETURN 
(
    SELECT Value = (SELECT 0,265433 UNION ALL
                    SELECT 0,0629412 UNION ALL
                    SELECT 0,671626)
)

but it throws an error:

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Upvotes: 0

Views: 260

Answers (2)

HABO
HABO

Reputation: 15852

Another syntax:

alter function [dbo].[GetEigenvector]()
  returns table as return
  select * from
    ( values (0.265433), (0.0629412), (0.671626) )
    as Eigenvector( Value );

Upvotes: 1

Lamak
Lamak

Reputation: 70668

You are messing up the syntax for giving an alias to your column:

ALTER FUNCTION [dbo].[GetEigenvector] 
(   

)
RETURNS TABLE 
AS
RETURN 
(
   SELECT 0.265433 AS Value
   UNION ALL
   SELECT 0.0629412 
   UNION ALL
   SELECT 0.671626
)

Upvotes: 1

Related Questions