Blog: T-SQL pocket knife code - 1.sql

USE [SitecoreWebsiteSitecore_Master]
GO
/****** Object:  UserDefinedFunction [dbo].[SC_GetName]    Script Date: 06/21/2012 21:59:44 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SC_GetName]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[SC_GetName]
GO
/****** Object:  UserDefinedFunction [dbo].[SC_GetPath]    Script Date: 06/21/2012 21:59:44 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SC_GetPath]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[SC_GetPath]
GO
/****** Object:  UserDefinedFunction [dbo].[SC_GetPath]    Script Date: 06/21/2012 21:59:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SC_GetPath]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'-- =============================================
-- Author:		Robin Hermanussen
-- Create date: 2012-06-21
-- Description:	Takes a Sitecore ID and resolves the path to the item that it corresponds with
-- =============================================
CREATE FUNCTION [dbo].[SC_GetPath] 
(
	@ItemID [uniqueidentifier]
)
RETURNS varchar(MAX)
AS
BEGIN
	DECLARE @Result varchar(MAX)

	with scpath(Name, ParentID)
	as
	(
		select Cast(a.Name as varchar(MAX)), a.ParentID
		from [Items] a
		where a.ID = @ItemID
		union all
		select Cast(b.Name + ''/'' + c.Name as varchar(MAX)), b.ParentID
		from [Items] b
		  inner join scpath c on b.ID = c.ParentID
		where c.ParentID is not null
	)

	select top 1 @Result = ''/'' + d.Name from scpath d
	where d.ParentID = ''11111111-1111-1111-1111-111111111111''

	RETURN @Result

END
'
END
GO
/****** Object:  UserDefinedFunction [dbo].[SC_GetName]    Script Date: 06/21/2012 21:59:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SC_GetName]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'-- =============================================
-- Author:		Robin Hermanussen
-- Create date: 2012-06-21
-- Description:	Takes a Sitecore ID and resolves the path of the item
-- =============================================
CREATE FUNCTION [dbo].[SC_GetName]
(
	@ItemID [uniqueidentifier]
)
RETURNS varchar(MAX)
AS
BEGIN
	DECLARE @Result varchar(MAX)

	select top 1 @Result = [Name]
	from [Items]
	where [ID] = @ItemID

	RETURN @Result

END
'
END
GO