java 存储过程 数组_存储过程处理字符串数组的方法

存储过程中传递数组为参数，但是可以把数组内容拼成字符串，然后再存储过程中解析出来处理。

USE [RecommendationTest]

GO

/****** Object: StoredProcedure [dbo].[Get_Recommendation_By_CategoryID]    Script Date: 09/26/2010 16:05:23 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:  

-- Create date:

-- Description:

-- =============================================

ALTER procedure [dbo].[Get_Recommendation_By_CategoryID]

(

@CategoryIDArray   NVARCHAR(max),--categoryID组成的数组字符串，以逗号隔开

@requireCount int --每个类需要显示数据的条数

)

AS

BEGIN

DECLARE @categoryID    nvarchar(max)

DECLARE    @i          INT

DECLARE    @len        INT

IF (@CategoryIDArray IS NULL) OR (LTRIM(@CategoryIDArray) = '')

RETURN

WHILE CHARINDEX(',',@CategoryIDArray) > 0

BEGIN

SET @len = LEN(@CategoryIDArray)

SET @i = CHARINDEX(',', @CategoryIDArray)

SET @categoryID = LEFT(@CategoryIDArray, @i-1)

print @categoryID

--sql语句,从该类中推荐score比较高的@requireCount个资源

select Top (@requireCount) *

from dbo.RecommendationByCategoryID t

where t.categoryID=@categoryID

order by t.score DESC

SET @CategoryIDArray = RIGHT(@CategoryIDArray, @len - @i)

END

SET @categoryID = @CategoryIDArray