T-SQL存储过程:获取属性名称的HTML标签
根据传入属性列,获取属性名,并组成HTML
ALTER PROCEDURE [dbo].[w_Category_GetNamesByCPVIDs]
@CateIDList nvarchar(300)
AS
DECLARE
@GardIndex int,
@CacheStr nvarchar(300),
@CacheID nvarchar(10),
@CacheName nvarchar(50),
@RtnStr nvarchar(4000),
@CID int,
@CacheNameCN nvarchar(50),
@CACHE_CPID int
BEGIN
SET @RtnStr='';
SET @GardIndex=0;
SET @CacheStr=@CateIDList;
--只有一个属性
IF(CHARINDEX('|',@CacheStr)=LEN(@CacheStr))
BEGIN
SET @GardIndex=CHARINDEX('|',@CacheStr)
SET @CacheID=SUBSTRING(@CacheStr,0,LEN(@CacheStr))
SET @CacheName=(SELECT CPVName FROM w_CateProValue WHERE CPVID=CAST(@CacheID AS int))
SET @CacheNameCN=(SELECT a.CPNameCN from w_CategoryProperty a,w_CateproValue b where b.CPVID=@CacheID and a.CPID=b.CPID)
SET @CacheStr='|';
SET @CACHE_CPID=(SELECT CPID FROM w_CateProValue WHERE CPVID=@CacheID);
SET @RtnStr='<font style="font-weight:lighter; color:#000000;"> > </font><font style="font-weight:normal; color:#8c0000;">'+@CacheName+'</font>';
END;
ELSE
BEGIN
WHILE(@GardIndex!=LEN(@CacheStr))
BEGIN
SET @GardIndex=CHARINDEX('|',@CacheStr);
SET @CacheID=SUBSTRING(@CacheStr,0,@GardIndex);
SET @CacheName=(SELECT CPVName FROM w_CateProValue WHERE CPVID=CAST(@CacheID AS int))
SET @CacheNameCN=(SELECT a.CPNameCN from w_CategoryProperty a,w_CateproValue b where b.CPVID=@CacheID and a.CPID=b.CPID)
SET @CacheStr=SUBSTRING(@CacheStr,@GardIndex+1,LEN(@CacheStr)-@GardIndex)
if(@CacheName is not null)
BEGIN
SET @CACHE_CPID=(SELECT CPID FROM w_CateProValue WHERE CPVID=@CacheID);
SET @RtnStr=@RtnStr+'<font style="font-weight:lighter; color:#000000;"> > </font><a name='''+@CacheID+''' id='''+CAST(@CACHE_CPID AS nvarchar)+
''' href=''javascript:Add('+@CacheID+',"'+@CacheName+'",'+CAST(@CACHE_CPID AS nvarchar)+',1)''><font onmouseover="this.style.color=''#8c0000''" onmouseout="this.style.color=''#9a9a9a''" style="font-weight:normal; color:#9a9a9a;">'+@CacheName+'</font></a>';
END
END
END
PRINT(@CacheName)
IF(@GardIndex=LEN(@CacheStr) AND @CacheStr!='|')
BEGIN
SET @CacheID=SUBSTRING(@CacheStr,0,@GardIndex);
SET @CacheName=(SELECT CPVName FROM w_CateProValue WHERE CPVID=CAST(@CacheID AS int))
SET @CacheNameCN=(SELECT a.CPNameCN from w_CategoryProperty a,w_CateproValue b where b.CPVID=@CacheID and a.CPID=b.CPID)
SET @CACHE_CPID=(SELECT CPID FROM w_CateProValue WHERE CPVID=@CacheID);
SET @RtnStr=@RtnStr+'<font style="font-weight:lighter; color:#000000;"> > </font><font style="font-weight:normal; color:#8c0000;">'+@CacheName+'</font>';
END
SELECT @RtnStr AS CateList;
END
@CateIDList nvarchar(300)
AS
DECLARE
@GardIndex int,
@CacheStr nvarchar(300),
@CacheID nvarchar(10),
@CacheName nvarchar(50),
@RtnStr nvarchar(4000),
@CID int,
@CacheNameCN nvarchar(50),
@CACHE_CPID int
BEGIN
SET @RtnStr='';
SET @GardIndex=0;
SET @CacheStr=@CateIDList;
--只有一个属性
IF(CHARINDEX('|',@CacheStr)=LEN(@CacheStr))
BEGIN
SET @GardIndex=CHARINDEX('|',@CacheStr)
SET @CacheID=SUBSTRING(@CacheStr,0,LEN(@CacheStr))
SET @CacheName=(SELECT CPVName FROM w_CateProValue WHERE CPVID=CAST(@CacheID AS int))
SET @CacheNameCN=(SELECT a.CPNameCN from w_CategoryProperty a,w_CateproValue b where b.CPVID=@CacheID and a.CPID=b.CPID)
SET @CacheStr='|';
SET @CACHE_CPID=(SELECT CPID FROM w_CateProValue WHERE CPVID=@CacheID);
SET @RtnStr='<font style="font-weight:lighter; color:#000000;"> > </font><font style="font-weight:normal; color:#8c0000;">'+@CacheName+'</font>';
END;
ELSE
BEGIN
WHILE(@GardIndex!=LEN(@CacheStr))
BEGIN
SET @GardIndex=CHARINDEX('|',@CacheStr);
SET @CacheID=SUBSTRING(@CacheStr,0,@GardIndex);
SET @CacheName=(SELECT CPVName FROM w_CateProValue WHERE CPVID=CAST(@CacheID AS int))
SET @CacheNameCN=(SELECT a.CPNameCN from w_CategoryProperty a,w_CateproValue b where b.CPVID=@CacheID and a.CPID=b.CPID)
SET @CacheStr=SUBSTRING(@CacheStr,@GardIndex+1,LEN(@CacheStr)-@GardIndex)
if(@CacheName is not null)
BEGIN
SET @CACHE_CPID=(SELECT CPID FROM w_CateProValue WHERE CPVID=@CacheID);
SET @RtnStr=@RtnStr+'<font style="font-weight:lighter; color:#000000;"> > </font><a name='''+@CacheID+''' id='''+CAST(@CACHE_CPID AS nvarchar)+
''' href=''javascript:Add('+@CacheID+',"'+@CacheName+'",'+CAST(@CACHE_CPID AS nvarchar)+',1)''><font onmouseover="this.style.color=''#8c0000''" onmouseout="this.style.color=''#9a9a9a''" style="font-weight:normal; color:#9a9a9a;">'+@CacheName+'</font></a>';
END
END
END
PRINT(@CacheName)
IF(@GardIndex=LEN(@CacheStr) AND @CacheStr!='|')
BEGIN
SET @CacheID=SUBSTRING(@CacheStr,0,@GardIndex);
SET @CacheName=(SELECT CPVName FROM w_CateProValue WHERE CPVID=CAST(@CacheID AS int))
SET @CacheNameCN=(SELECT a.CPNameCN from w_CategoryProperty a,w_CateproValue b where b.CPVID=@CacheID and a.CPID=b.CPID)
SET @CACHE_CPID=(SELECT CPID FROM w_CateProValue WHERE CPVID=@CacheID);
SET @RtnStr=@RtnStr+'<font style="font-weight:lighter; color:#000000;"> > </font><font style="font-weight:normal; color:#8c0000;">'+@CacheName+'</font>';
END
SELECT @RtnStr AS CateList;
END