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?
-
Are you using SQL Server and if yes then what version?Radu Gheorghiu– Radu Gheorghiu2014-08-05 10:20:47 +00:00Commented Aug 5, 2014 at 10:20
Add a comment
|
1 Answer
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
2 Comments
nnmmss
Thank you for answer, I am just newbie in that subject. where should create TableTypeand fn_test_tab? TableType in User-Defined Table Types ? fn_test_tab in Scalar Valued Functions?is that correct?
Rahul
Yes, just follow the same way/same sequence I have posted in answer. Also obviously, all must under same DB.