This blog will explain one of the most important question in interview?
Is How to Join Table Valued Function with Table?
For Ex
- Create table Emp as
- CREATE TABLE [dbo].[Emp](
- [ID] [int] IDENTITY(1,1) NOT NULL,
- [Name] [varchar](50) NULL,
- [City] [varchar](50) NULL,
- CONSTRAINT [PK_Emp] PRIMARY KEY CLUSTERED
- (
- [ID] ASC
- )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
- ) ON [PRIMARY]
- GO
And Insert Record into Emp table as
- Create Table value function as
- GO
-
-
-
-
-
- Create FUNCTION [dbo].[fn_Salary]
- (
- @id Int
- )
- RETURNS TABLE
- AS
- RETURN
- (
- SELECT Salary from Salary where ID=@id
- )
Then we can join these table value function and table with in the help of "Cross Apply" as
- select e.ID,e.Name,e.City,x.Salary from Emp e left join
- (
- select ID,Name,Salary from Emp cross apply [dbo].fn_Salary(ID)
- )x on x.ID=e.ID
And show Result as