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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy