Scalar Function with select query as argument

Question

is it possible to have a function in SQL which accepts the result of a select Query? like SCalarFunc(Select * from CustomQuery)

and also I want to make a another select query back from the Function?

can anybody give a sample?

Answer 1

If you are using SQL Server 2008 and above then you can follow below sample code using User-Defined Table Types variable

-- Create first function to return the result of select query

create function fn_test()
returns table
as
return (select * from tab);

-- test it once
select * from dbo.fn_test()


--create a user defined table types
CREATE TYPE TableType 
AS TABLE (id int not null,
name varchar(10)); 


--create your second level function passing the 
--result of first function as table
CREATE FUNCTION fn_test_tab(@tabname TableType READONLY)
RETURNS VARCHAR(10)
AS
BEGIN
    DECLARE @name VARCHAR(10)

    SELECT @name = name FROM @tabname where id = 1;
    RETURN @name
END

--define a variable of created table type
DECLARE @tab TableType;

-- fill the data from first function
insert into @tab select * from dbo.fn_test();

--call your second function passing the table variable
SELECT dbo.fn_test_tab(@tab);

Sidenote: Tested in SQL server 2008 R2 Express and it works fine. Use of Table type concept is taken from Pass table as parameter into sql server UDF