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

时间：2010-10-21 来源：jiavaz

存储过程中传递数组为参数，但是可以把数组内容拼成字符串，然后再存储过程中解析出来处理。 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:   <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <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