sql 截取100，101，102， 字符串到临时表

-------------------------------------------------------------------------------------------------------------------------------------
declare @BENCODE_WEC_LIMIT varchar(100)
select @BENCODE_WEC_LIMIT='1,2,3,4,5,10,'
create table #BENCODETB(
bencode varchar(100)
)
EXEC EC_DIVIDE_BUNCH_PARAMETERS @BENCODE_WEC_LIMIT , ',','','','STRING','#BENCODETB','Y'
select *from #BENCODETB
drop table #BENCODETB

-------------------------------------------------------------------------------------------------------------------------------------

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[EC_DIVIDE_BUNCH_PARAMETERS]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[EC_DIVIDE_BUNCH_PARAMETERS]
GO

CREATE      PROCEDURE [dbo].[EC_DIVIDE_BUNCH_PARAMETERS]
    @parameters varchar(8000)='AB,CKD,FDAKF,',
    @seperatorflag char(1) = ',',
    @defaultValue varchar(255) = '',
    @LS_COLUMNNAME VARCHAR(100)='',
    @LS_COLUMNTYPE VARCHAR(50)='STRING',
    @LS_TABLENAME VARCHAR(255)='#speratedtmp',
    @LS_TRMSTR CHAR(1)='N'
AS
SET NOCOUNT ON  
IF(dbo.EMPTY(@LS_TABLENAME)=0)
BEGIN
    SELECT @LS_TABLENAME = '#speratedtmp'
END

SELECT @parameters = LTRIM(RTRIM(@parameters))
DECLARE @LS_INS_HEAD_SQL VARCHAR(800)
SELECT @LS_INS_HEAD_SQL = "INSERT INTO " + @LS_TABLENAME

IF(dbo.EMPTY(@LS_COLUMNNAME)<>0)
BEGIN
    SELECT @LS_INS_HEAD_SQL =@LS_INS_HEAD_SQL + " ( " + @LS_COLUMNNAME + " ) "
END

DECLARE @LS_INS_VALUE_SQL VARCHAR(1000)
IF(dbo.EMPTY(@parameters)=0)
BEGIN
    SELECT @LS_INS_VALUE_SQL = " ( " + DBO.EC_CONVERT_SQLVALUE(@defaultValue,@LS_COLUMNTYPE,@defaultValue,@LS_TRMSTR) + " )"
    EXEC (@LS_INS_HEAD_SQL + " VALUES "  + @LS_INS_VALUE_SQL)
     RETURN
end

IF(dbo.EMPTY(@parameters)=0)
BEGIN
    SELECT @seperatorflag  = ','
END

IF(CHARINDEX(@seperatorflag,@parameters,0)=0)
begin
    SELECT @LS_INS_VALUE_SQL = " ( " + DBO.EC_CONVERT_SQLVALUE(@parameters,@LS_COLUMNTYPE,@defaultValue,@LS_TRMSTR) + " )"
    EXEC (@LS_INS_HEAD_SQL + " VALUES "  + @LS_INS_VALUE_SQL)
    RETURN
end

    WHILE(CHARINDEX(@seperatorflag,@parameters,0)<>0)
    BEGIN
        SELECT @LS_INS_VALUE_SQL = " ( " + DBO.EC_CONVERT_SQLVALUE(substring(@parameters,0,CHARINDEX(@seperatorflag,@parameters)),@LS_COLUMNTYPE,@defaultValue,@LS_TRMSTR) + " )"
        EXEC (@LS_INS_HEAD_SQL + " VALUES "  + @LS_INS_VALUE_SQL)
        SET @parameters=substring(@parameters,CHARINDEX(@seperatorflag,@parameters)+1,len(@parameters))
    END

    SELECT @LS_INS_VALUE_SQL = " ( " + DBO.EC_CONVERT_SQLVALUE(@parameters,@LS_COLUMNTYPE,@defaultValue,@LS_TRMSTR) + " )"
    EXEC (@LS_INS_HEAD_SQL + " VALUES "  + @LS_INS_VALUE_SQL)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

-------------------------------------------------------------------------------------------------------------------------------------
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[EMPTY]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[EMPTY]
GO
CREATE   FUNCTION dbo.EMPTY ( @ls_arg varchar(255) ) RETURNS int
AS
BEGIN
    if((@ls_arg is NULL) or @ls_arg='') return 0
    return 1
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

-------------------------------------------------------------------------------------------------------------------------------------
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[EC_CONVERT_SQLVALUE]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[EC_CONVERT_SQLVALUE]
GO
CREATE   FUNCTION dbo.EC_CONVERT_SQLVALUE
(
    @LS_SQLVALUE     VARCHAR(255)    =NULL,
    @LS_SQLTYPE     VARCHAR(100)    ='STRING',
    @LS_DEFAULT     VARCHAR(255)    =NULL,
    @LS_TRIMSTR     CHAR(1)        ='N'

) RETURNS VARCHAR(300)
AS
BEGIN
    DECLARE @LS_CONVSQLVALUE     VARCHAR(255)
    IF(dbo.EMPTY(@LS_SQLVALUE)=0) SELECT @LS_SQLVALUE = @LS_DEFAULT
    IF (@LS_TRIMSTR = 'Y')  SELECT @LS_SQLVALUE=LTRIM(RTRIM(@LS_SQLVALUE))
    SELECT @LS_CONVSQLVALUE = @LS_SQLVALUE
    IF(@LS_SQLTYPE = 'STRING') SELECT @LS_CONVSQLVALUE= "'" + @LS_SQLVALUE + "'"
    IF(@LS_SQLTYPE = 'DATE') SELECT @LS_CONVSQLVALUE= "'" + @LS_SQLVALUE + "'"
    RETURN @LS_CONVSQLVALUE
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

-------------------------------------------------------------------------------------------------------------------------------------