Turning a CTE into a function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Turning a CTE into a function

Author

Topic

craigwg
Posting Yak Master

154 Posts

Posted - 2010-02-04 : 12:26:09

I have a working CTE in a stored procedure that I need to turn into a function. From research I know its possible but I'm having a heck of a time figuring out how to do it. Can you help?

The plan is to insert an employee id (an email address) and the level of manager you want (A=ceo, b=vice president, c=regional manager, d=supervisor, etc). To run the current sp you enter this:

exec usp_findmanager 'craig@hotmail.com','b'

And you are returned with the vice president craig reports to.

Here is the stored procedure that runs this CTE:


USE [IDM_V]
GO
/****** Object:  StoredProcedure [dbo].[usp_findmanager]    Script Date: 02/04/2010 10:22:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Create date: February 1, 2010
-- Description:	Takes two inputs, employee UPN (email address) and 
-- the letter representing the manager level you want to see.  
-- For example enter: 
--
--		exec usp_findmanager 'anthony@hotmail.com', 'C'
-- 
-- to find the manager at level C for Anthony.
-- =============================================
ALTER PROCEDURE [dbo].[usp_findmanager] 
	-- Add the parameters for the stored procedure here
	@upn varchar(100), 
	@empgroup varchar (3)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here

WITH ORG (managerUPN,userprincipalName,[Level], employeegroup) AS
(SELECT  anchor.managerUPN,anchor.userprincipalName,1, employeegroup
	FROM IDM_Identities anchor
	WHERE anchor.userprincipalName = @upn 
 UNION ALL
 SELECT e.managerUPN, e.userprincipalName,Level +1, e.employeegroup
	FROM IDM_Identities e  
	JOIN ORG o on e.userprincipalName = o.managerUPN collate SQL_Latin1_General_CP1_CI_AS)
SELECT top 1 @upn as Employee, managerUPN as 'requested supervisor', employeegroup as Level
FROM org
WHERE employeegroup=@empgroup
ORDER BY LEVEL ASC --change from asc to desc to change priority level
END

So how can I turn this into a working funtion instead of a SP?

Craig Greenwood

Sachin.Nand

2937 Posts

Posted - 2010-02-04 : 12:28:46

You can use a table valued function.

PBUH

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-04 : 12:32:34

something like below

CREATE FUNCTION [dbo].[usp_findmanager] 
(
	@upn varchar(100), 
	@empgroup varchar (3)
)
RETURNS @RESULTS Table
(
Employee varchar(100),
supervisor varchar(100),
Level int
)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here

;WITH ORG (managerUPN,userprincipalName,[Level], employeegroup) AS
(SELECT  anchor.managerUPN,anchor.userprincipalName,1, employeegroup
	FROM IDM_Identities anchor
	WHERE anchor.userprincipalName = @upn 
 UNION ALL
 SELECT e.managerUPN, e.userprincipalName,Level +1, e.employeegroup
	FROM IDM_Identities e  
	JOIN ORG o on e.userprincipalName = o.managerUPN collate SQL_Latin1_General_CP1_CI_AS)
INSERT INTO @RESULTS
SELECT top 1 @upn as Employee, managerUPN as 'requested supervisor', employeegroup as Level
FROM org
WHERE employeegroup=@empgroup
ORDER BY LEVEL ASC --change from asc to desc to change priority level

RETURN
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources