0

I have a function that returns a table. It takes one parameter and returns one row.

I want to select the values from that function along with columns from another table in the same SELECT staement.

Something like this:

SELECT a.col1, a.col2, b.col1, b.col2
FROM tab1 a INNER JOIN
     func1(a.col1) b ON a.col1 = b.col1
WHERE a.col1 IN (123,456,789)

This doesn't work, but is there a way I can do this.

So, for example, if func 1 returns the following for each of the three values in the example:

col1  col2
123   abc
456   def
789   xyz

then I am expecting something like the following results from my query:

col1  col2  col1  col2
123   xxx   123   abc
456   yyy   456   def
789   zzz   789   xyz

I can do it like this, but I'd rather not call the function multiple times for each column I want from the function:

SELECT col1, col2, (SELECT col1 FROM func1(a.col1)), (SELECT col2 FROM func1(a.col1))
FROM tab1 a
WHERE a.col1 IN (123,456,789)
Graham
  • 7,807
  • 20
  • 69
  • 114
  • 3
    I think you are looking for `cross apply` instead of `inner join` if I'm not mistaken (which is the ANSI `lateral join` operator in T-SQL) –  Apr 14 '14 at 14:33
  • tried cross apply - did not work either – Graham Apr 14 '14 at 14:37
  • 1
    This is probably answered for you in [this Stackoverflow question][1]. [1]: http://stackoverflow.com/questions/4764994/sql-join-table-valued-function-with-table-where-table-field-is-a-function-input – James Jensen Apr 14 '14 at 14:44
  • My mistake, it was cross join that I had tried, cross apply is the answer - thank you – Graham Apr 14 '14 at 14:48

1 Answers1

0

this code is true 

alter  FUNCTION GETTABLE()
RETURNS  @rtnTable TABLE 
(
    SUM_CD nvarchar(15) NOT NULL,
    Name nvarchar(255) NOT NULL
)
AS
BEGIN
DECLARE @TempTable table (SUM_CD Nvarchar(15), name nvarchar(255))

insert into @TempTable(SUM_CD,name) values ('300001','Ahmed')
insert into @TempTable(SUM_CD,name) values ('300002','Mohamed')

insert into @rtnTable
select * from @TempTable
return
END

select * from   GLSUMS g inner join dbo.gettable() s on s.SUM_CD=g.SUM_CD

in SQL SERVER 2012

Ahmed Galal
  • 694
  • 7
  • 21