[SQL] 待整理3

浏览数：20 / 时间：2015年06月12日

Create proc procedureName
     @orderId int
    ,@name varchar(20)
    ,@count int
as
begin

    Declare @id int
    declare @tb table(postid int)
    BEGIN TRANSACTION
    insert into A value(@orderId)
    Select @id=@@identity
    SET @count = 0
    SELECT @count = count(*) FROM   dbo.f_split(‘A,B,C,D,E‘,‘,‘)
   WHILE @count > 0
   begin
     insert into @tb select @id
     set @count=@count-1
   end
    --select * from dbo.f_split(‘A,B,C,D,E‘,‘,‘)


    insert into zibiao values(select * from @tb,select * from dbo.f_split(‘A,B,C,D,E‘,‘,‘),select * from dbo.f_split(‘A,B,C,D,E‘,‘,‘))



    --Insert into B values(@id,@name)


    IF @@error <> 0 --发生错误
    BEGIN
        ROLLBACK TRANSACTION
        RETURN 0
    END
    ELSE
    BEGIN
        COMMIT TRANSACTION
        RETURN 1 --执行成功
    END
end

下面的sql文是个函数，功能是：分析字符串，根据特殊符号整理插入到一个表结构里

create function f_split
(
     @SourceSql varchar(8000)
    ,@StrSeprate varchar(10)
)
returns @temp table(a varchar(100))
--实现split功能 的函数
--date    :2005-4-20
--Author :Domino
as 
begin
    declare @i int
    set @SourceSql=rtrim(ltrim(@SourceSql))
    set @i=charindex(@StrSeprate,@SourceSql)
    while @i>=1
    begin
        insert @temp values(left(@SourceSql,@i-1))
        set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)
        set @i=charindex(@StrSeprate,@SourceSql)
    end
    if @SourceSql<>‘\‘
       insert @temp values(@SourceSql)
    return 
end