Reputation: 3002
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?
Upvotes: 2
Views: 4796
Reputation: 77934
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
Upvotes: 2