February 2, 2012 at 9:39 pm
Hi,
Need some help in below requirement.
I have a table called Emp.
CREATE TABLE [dbo].[Emp](
[EmployeeID] [int] IDENTITY(1,1) NOT NULL primary key,
[LastName] [nchar](30) NOT NULL,
[FirstName] [nchar](29) NOT NULL,
[MiddleInitial] [nchar](1) NULL,
[SSN] [char](11) NOT NULL,
[OtherColumns] [char](258) NOT NULL
)
SET IDENTITY_INSERT Emp ON
INSERT Emp(EmployeeID,LastName,FirstName,MiddleInitial,SSN,OtherColumns)
VALUES (1,'Stein','Nidprxmvtyjnat','','123-07-9951','Junk')
INSERT Emp(EmployeeID,LastName,FirstName,MiddleInitial,SSN,OtherColumns)
VALUES (2,'Eflin','Ysgphbplbhoksy','','327-07-9911','Junk')
INSERT Emp(EmployeeID,LastName,FirstName,MiddleInitial,SSN,OtherColumns)
VALUES (3,'Quint','Ysoawvtycuwv','','593-07-9871','Junk')
INSERT Emp(EmployeeID,LastName,FirstName,MiddleInitial,SSN,OtherColumns)
VALUES (4,'Chen','Mju','','750-07-9831','Junk')
INSERT Emp(EmployeeID,LastName,FirstName,MiddleInitial,SSN,OtherColumns)
VALUES (5,'Olphant','Tqbigir','','983-07-9791','Junk')
INSERT Emp(EmployeeID,LastName,FirstName,MiddleInitial,SSN,OtherColumns)
VALUES (6,'Anderson','Dreaxjktgvnhye','','250-07-9751','Junk')
INSERT Emp(EmployeeID,LastName,FirstName,MiddleInitial,SSN,OtherColumns)
VALUES (7,'Makai','Lnudwgnbtoyvix','','467-07-9711','Junk')
INSERT Emp(EmployeeID,LastName,FirstName,MiddleInitial,SSN,OtherColumns)
VALUES (8,'Yeong','Gttwynjpwb','','670-07-9671','Junk')
INSERT Emp(EmployeeID,LastName,FirstName,MiddleInitial,SSN,OtherColumns)
VALUES (9,'Kahrmann','Svjampio','','983-07-9631','Junk')
INSERT Emp(EmployeeID,LastName,FirstName,MiddleInitial,SSN,OtherColumns)
VALUES (10,'Wolfmule','Uusfynrfejusqx','','293-07-9591','Junk')
I have created a table valued function
CREATE FUNCTION testfn (@empid int)
RETURNS TABLE
AS
RETURN
( select * from emp where employeeid = @empid
);
GO
select * from testfn(1)
select * from testfn(2)
select * from testfn(3)
Now currectly what i am doing is, i wanted the output returned by the table valued function to store in a #temp table.
This is how i am doing.
CREATE TABLE #temp(
[EmployeeID] [int] NOT NULL,
[LastName] [nchar](30) NOT NULL,
[FirstName] [nchar](29) NOT NULL,
[MiddleInitial] [nchar](1) NULL,
[SSN] [char](11) NOT NULL,
[OtherColumns] [char](258) NOT NULL
)
insert into #temp
select * from testfn(1)
insert into #temp
select * from testfn(2)
insert into #temp
select * from testfn(3)
select * from testfn(emp
Now my requirement is, i will not pass the employeeid 1,2,3 explicitly.
Now i wanted to provide the columnname as parameter to my table valued function so that it automatically populates the values for each distinct employeeid's in temptbl.
insert into #temp
select * testfn(employeeid) from Emp;
How can i accomplish that.
Any help would be greatly appreciated.
Thanks in Advance.
February 3, 2012 at 5:14 am
Try CROSS APPLY
SELECT t.*
FROM dbo.EMP AS e
CROSS APPLY dbo.testfn(e.Employeeid) AS t;
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 3, 2012 at 9:41 pm
Thanks grant. Thanks for the help.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply