SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SelectSide](@career nchar(2))
AS
CREATE TABLE #tmp
(
KeyWord nvarchar(50),
Website1 nvarchar(50),
URL1 varchar(100),
Introduce1 nvarchar(max),
Website2 nvarchar(50),
URL2 varchar(100),
Introduce2 nvarchar(max),
Website3 nvarchar(50),
URL3 varchar(100),
Introduce3 nvarchar(max),
Website4 nvarchar(50),
URL4 varchar(100),
Introduce4 nvarchar(max),
Website5 nvarchar(50),
URL5 varchar(100),
Introduce5 nvarchar(max),
Website6 nvarchar(50),
URL6 varchar(100),
Introduce6 nvarchar(max),
Website7 nvarchar(50),
URL7 varchar(100),
Introduce7 nvarchar(max),
Website8 nvarchar(50),
URL8 varchar(100),
Introduce8 nvarchar(max),
Website9 nvarchar(50),
URL9 varchar(100),
Introduce9 nvarchar(max)
)
DECLARE @keyword nvarchar(max),
@keyTmp nvarchar(50),
@url varchar(100),
@url1 varchar(100),
@website nvarchar(50),
@website1 nvarchar(50),
@introduce nvarchar(max),
@introduce1 nvarchar(max),
@i int,
@iBegin int,
@iEnd int,
@switch bit
SELECT @keyword = KeyWord
FROM TCareer
WHERE (Career = @career)
SET @switch = 1
SET @iBegin = 1
SET @iEnd = CHARINDEX(',', @keyword, 1)
WHILE @iEnd > 0
BEGIN
SET @keyTmp = SUBSTRING(@keyword, @iBegin, @iEnd - @iBegin)
DECLARE cur1 CURSOR FOR
SELECT TOP (9) URL, Website, Introduce
FROM TAddress
WHERE (Career = @career) AND (KeyWord = @keyTmp)
ORDER BY AddTime DESC
OPEN cur1
FETCH NEXT FROM cur1 INTO @url,@website,@introduce
SET @i = 1
WHILE @@FETCH_STATUS = 0
BEGIN
SET @introduce1 = REPLACE(@introduce,'''','''''')
SET @url1 = REPLACE(@url,'''','''''')
SET @website1 = REPLACE(@website,'''','''''')
IF @i = 1
INSERT INTO #tmp (KeyWord,Website1,URL1,Introduce1)
VALUES (@keyTmp,@website,@url,@introduce)
ELSE
EXECUTE ('UPDATE #tmp SET URL'+ @i +'='''+ @url1 +''',Website'+ @i +' = N'''+ @website1 +''',Introduce'+ @i +' = N'''+ @introduce1 +'''
WHERE (KeyWord = N'''+ @keyTmp +''')')
SET @i = @i + 1
FETCH NEXT FROM cur1 INTO @url,@website,@introduce
END
CLOSE cur1
DEALLOCATE cur1
SET @iBegin = @iEnd + 1
SET @iEnd = CHARINDEX(',',@keyword,@iBegin)
IF @iEnd = 0 AND @switch = 1
BEGIN
SET @iEnd = LEN(@keyword) + 1
SET @switch = 0
END
END
SELECT * FROM #tmp
RETURN
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
