sql in

USE [CNPR.Core]
GO
/****** Object:  UserDefinedFunction [dbo].[fnSplit]    Script Date: 08/15/2013 12:51:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Author:        Alex.Hon
-- Create date:  <2010/10/19>
-- Description:     <string.split()拆分字符串>
-- =============================================
ALTER FUNCTION [dbo].[fnSplit]
(
 @string varchar(2000),        --1,2,3,45,
 @separator varchar(1)=','
)
RETURNS @temp TABLE
(
    Item varchar(1000)
)
AS
BEGIN
    DECLARE @Item varchar(2000)
    DECLARE @CurrentIndex  int
    DECLARE @NextIndex int
    DECLARE @Length  int    --字符串的长度
    SET @CurrentIndex=1
    SET @Length=DATALENGTH(@string)
   
    IF @string IS NOT NULL
        BEGIN
            WHILE @CurrentIndex<@Length
                BEGIN
                    --CHARINDEX(子串,被搜索的字符串)
                    SET @NextIndex=CHARINDEX(@separator,@string,@CurrentIndex)
                    SET @Item=SUBSTRING(@string,@CurrentIndex,@NextIndex-@CurrentIndex)
                    SET @CurrentIndex=@NextIndex+1
                    --把临时变量的值放到要返回的表中
                    INSERT INTO @temp VALUES(@Item)
                END
        END
    RETURN
END

 

 

 

 

 

  select Id from SSI_Policy_TD  where IsDeleted=@IsDeleted and IsDeleted=@IsDeleted and Location IN   (SELECT ITEM FROM fnSplit(@LocationSQL,','))