CREATE PROCEDURE UP_GetKeywordInfo
@Keyword varchar(50)
ASDECLARE @StrTemp VarChar(1000)
DECLARE @Count int
DECLARE @Price intSET @Price=0
SET @Count=1
SET @StrTemp=''
WHILE(@Count<=6)CASE @Count
WHEN 1 THEN SET @Price=1000
WHEN 2 THEN SET @Price=1000
WHEN 3 THEN SET @Price=800
WHEN 4 THEN SET @Price=800
WHEN 5 THEN SET @Price=600
WHEN 6 THEN SET @Price=600
ELSE @Price=0
ENDBEGINIF(SELECT COUNT(*) from htfly_Keywords AS k JOIN htfly_Member_Keywords AS mk ON mk.Keyword=k.Keyword JOIN htfly_Member AS member ON member.MemberID=mk.MemberID WHERE mk.Rank=@Count AND mk.Keyword=''+@Keyword+'')=0
SET @StrTemp='SELECT '+CAST(@Count AS VarChar)+' AS Rank,''无'' AS CompanyName,0 AS Price,0 AS State,'''+@Keyword+''' AS Keyword,''--'' AS StartDate,''--'' AS EndDate UNION '+@StrTemp
ELSE
SET @StrTemp='SELECT '+CAST(@Count AS VarChar)+' AS Rank,member.CompanyName,800 AS Price,mk.State,k.Keyword,mk.StartDate,mk.EndDate from htfly_Keywords AS k JOIN htfly_Member_Keywords AS mk ON mk.Keyword=k.Keyword JOIN htfly_Member AS member ON member.MemberID=mk.MemberID WHERE mk.Rank='+CAST(@Count AS VarChar)+' AND mk.Keyword='''+@Keyword+''' UNION '+@StrTempSET @Count=@Count+1ENDSET @StrTemp=LEFT(@StrTemp,LEN(@StrTemp)-5)
--PRINT @StrTemp
EXEC (@StrTemp)
GO
--------------------------------
语法通不过。说case 那里错误。求解?
@Keyword varchar(50)
ASDECLARE @StrTemp VarChar(1000)
DECLARE @Count int
DECLARE @Price intSET @Price=0
SET @Count=1
SET @StrTemp=''
WHILE(@Count<=6)CASE @Count
WHEN 1 THEN SET @Price=1000
WHEN 2 THEN SET @Price=1000
WHEN 3 THEN SET @Price=800
WHEN 4 THEN SET @Price=800
WHEN 5 THEN SET @Price=600
WHEN 6 THEN SET @Price=600
ELSE @Price=0
ENDBEGINIF(SELECT COUNT(*) from htfly_Keywords AS k JOIN htfly_Member_Keywords AS mk ON mk.Keyword=k.Keyword JOIN htfly_Member AS member ON member.MemberID=mk.MemberID WHERE mk.Rank=@Count AND mk.Keyword=''+@Keyword+'')=0
SET @StrTemp='SELECT '+CAST(@Count AS VarChar)+' AS Rank,''无'' AS CompanyName,0 AS Price,0 AS State,'''+@Keyword+''' AS Keyword,''--'' AS StartDate,''--'' AS EndDate UNION '+@StrTemp
ELSE
SET @StrTemp='SELECT '+CAST(@Count AS VarChar)+' AS Rank,member.CompanyName,800 AS Price,mk.State,k.Keyword,mk.StartDate,mk.EndDate from htfly_Keywords AS k JOIN htfly_Member_Keywords AS mk ON mk.Keyword=k.Keyword JOIN htfly_Member AS member ON member.MemberID=mk.MemberID WHERE mk.Rank='+CAST(@Count AS VarChar)+' AND mk.Keyword='''+@Keyword+''' UNION '+@StrTempSET @Count=@Count+1ENDSET @StrTemp=LEFT(@StrTemp,LEN(@StrTemp)-5)
--PRINT @StrTemp
EXEC (@StrTemp)
GO
--------------------------------
语法通不过。说case 那里错误。求解?
WHEN 1 THEN 1000
WHEN 2 THEN 1000
WHEN 3 THEN 800
WHEN 4 THEN 00
WHEN 5 THEN 600
WHEN 6 THEN 600
ELSE 0
ENDSELECT @Price
@Keyword varchar(50)
ASDECLARE @StrTemp VarChar(1000)
DECLARE @Count int
DECLARE @Price intSET @Price=0
SET @Count=1
SET @StrTemp=''
WHILE(@Count<=6)select @Price=(CASE @Count
WHEN 1 THEN 1000
WHEN 2 THEN 1000
WHEN 3 THEN 800
WHEN 4 THEN 800
WHEN 5 THEN 600
WHEN 6 THEN 600
ELSE 0 eND)BEGINIF(SELECT COUNT(*) from htfly_Keywords AS k JOIN htfly_Member_Keywords AS mk ON mk.Keyword=k.Keyword JOIN htfly_Member AS member ON member.MemberID=mk.MemberID WHERE mk.Rank=@Count AND mk.Keyword=''+@Keyword+'')=0
SET @StrTemp='SELECT '+CAST(@Count AS VarChar)+' AS Rank,''无'' AS CompanyName,0 AS Price,0 AS State,'''+@Keyword+''' AS Keyword,''--'' AS StartDate,''--'' AS EndDate UNION '+@StrTemp
ELSE
SET @StrTemp='SELECT '+CAST(@Count AS VarChar)+' AS Rank,member.CompanyName,800 AS Price,mk.State,k.Keyword,mk.StartDate,mk.EndDate from htfly_Keywords AS k JOIN htfly_Member_Keywords AS mk ON mk.Keyword=k.Keyword JOIN htfly_Member AS member ON member.MemberID=mk.MemberID WHERE mk.Rank='+CAST(@Count AS VarChar)+' AND mk.Keyword='''+@Keyword+''' UNION '+@StrTempSET @Count=@Count+1ENDSET @StrTemp=LEFT(@StrTemp,LEN(@StrTemp)-5)
--PRINT @StrTemp
EXEC (@StrTemp)
GO
WHEN 1 THEN SET @Price=1000
WHEN 2 THEN SET @Price=1000
WHEN 3 THEN SET @Price=800
WHEN 4 THEN SET @Price=800
WHEN 5 THEN SET @Price=600
WHEN 6 THEN SET @Price=600
ELSE =0
END 这里错了应该是这样
SET @Price=CASE @Count
WHEN 1 THEN 1000 WHEN 2 THEN 1000 WHEN 3 THEN 800
WHEN 4 THEN 800 WHEN 5 THEN 600 WHEN 6 THEN 600
ELSE 0 END