存储过程提取Umbraco中的内容

@ParentNodeName和@ContentName 都是Document Type的名字

@ParentNodeName   --为父节点

@ContentName          --当前节点，所要取得的内容

CREATE PROCEDURE [dbo].[usp_getContentData]

@ParentNodeName NVARCHAR(255),
@ContentName NVARCHAR(255) 
AS
BEGIN
SET NOCOUNT ON;
DECLARE @CURNODEID INT
DECLARE @CURCONTENTTYPEID INT
DECLARE @VERSIONID NVARCHAR(200)




SELECT @CURCONTENTTYPEID=curcontent.contentType,@CURNODEID=curcontent.nodeid ,@VERSIONID=versionId
FROM umbracoNode 
INNER JOIN cmsDocument ON umbracoNode.Id = cmsDocument.NodeId and Published = 1--pubished version //and newest = 1 --draft version
INNER JOIN cmsContent ON umbracoNode.Id = cmsContent.NodeId 
INNER JOIN umbracoNode DocType ON cmsContent.contentType = DocType.Id 
and DocType.nodeObjectType = 'a2cb7800-f571-4787-9638-bc48539a0efb'
INNER JOIN umbracoNode PARENTNODE ON umbracoNode.PARENTID=PARENTNODE.ID
AND UPPER(PARENTNODE.TEXT)=@ParentNodeName
INNER JOIN cmsContent curcontent ON curcontent.nodeid=umbracoNode.Id
INNER JOIN cmsContentTYPE CURDOCTYPE ON curcontent.contentType=CURDOCTYPE.NODEID
AND UPPER(CURDOCTYPE.ALIAS)=@ContentName
where umbracoNode.nodeObjectType = 'c66ba18e-eaf3-4cff-8a22-41b16d66a972'
AND umbracoNode.ParentId <> -20


SELECT UPPER(CPT.ALIAS) as ALIAS,CPDRESULT.DATAINT,CPDRESULT.DATADATE,CPDRESULT.DATANVARCHAR,CPDRESULT.DATANTEXT
FROM cmsPropertyData CPDRESULT
INNER JOIN cmsPropertyType CPT ON CPT.ID=CPDRESULT.PROPERTYTYPEID
INNER JOIN cmsPropertyData CPD1 ON CPDRESULT.VERSIONID=CPD1.VERSIONID
AND CPD1.ID=
(SELECT MAX(CPD.ID)
FROM cmsPropertyType CPT
INNER JOIN cmsPropertyData CPD ON CPT.ID=CPD.PROPERTYTYPEID
AND CPT.CONTENTTYPEID=@CURCONTENTTYPEID
AND CPD.CONTENTNODEID=@CURNODEID
AND CPD.versionId=@VERSIONID
)
ORDER BY 1


END