/******  2012-8-15 更新 4.8-4.9 开始******/
/*
如何使用：    
declare @str1 varchar(max), @str2 varchar(max), @str3 varchar(max)  
set @str1 = '1,2,3'  
set @str2 = '1###2###3'  
set @str3 = '1###2###3###'  
select [Value] from [dbo].[SplitString](@str1, ',', 1)  
select [Value] from [dbo].[SplitString](@str2, '###', 1)  
select [Value] from [dbo].[SplitString](@str3, '###', 0)  
*/
IF EXISTS (SELECT *
			FROM   dbo.sysobjects
			WHERE  id = Object_id(N'[dbo].[SplitString]')
					AND xtype IN (N'FN',N'IF',N'TF'))
  DROP FUNCTION [dbo].[SplitString]
GO
CREATE function [dbo].[SplitString]  
(  
    @Input varchar(8000), --input string to be separated  
    @Separator varchar(8000)=',', --a string that delimit the substrings in the input string  
    @RemoveEmptyEntries bit=1 --the return value does not include array elements that contain an empty string  
)  
returns @TABLE table   
(  
    [Id] int identity(1,1),  
    [Value] varchar(8000)  
)   
as  
begin   
    declare @Index int, @Entry varchar(8000)  
    set @Index = charindex(@Separator,@Input)  
	--add @Separator
	if (len(@Input)>0  and @Index>0 )
		begin  
		   set @Input=@Input +@Separator
		end     
    while (@Index>0)  
    begin  
        set @Entry=ltrim(rtrim(substring(@Input, 1, @Index-1)))           
        if (@RemoveEmptyEntries=0) or (@RemoveEmptyEntries=1 and @Entry<>'')  
            begin  
                insert into @TABLE([Value]) Values(@Entry)  
            end     
		set @Input = substring(@Input, @Index+1, len(@Input))
        set @Index = charindex(@Separator, @Input)  
    end  
      
    set @Entry=ltrim(rtrim(@Input))  
    if (@RemoveEmptyEntries=0) or (@RemoveEmptyEntries=1 and @Entry<>'')  
        begin  
            insert into @TABLE([Value]) Values(@Entry)  
        end   
    return  
 end   
GO
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[e_newsban]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE TABLE [dbo].[e_newsban] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[newsid] [int] NULL ,
	[editionid] [int] NULL 
) ON [PRIMARY]
GO
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PK_e_newsban]') and OBJECTPROPERTY(id, N'IsPrimaryKey') = 1)
ALTER TABLE [dbo].[e_newsban] WITH NOCHECK ADD 
	CONSTRAINT [PK_e_newsban] PRIMARY KEY  CLUSTERED 
	(
	   [id]
	) ON [PRIMARY] 
GO
if not  exists(select * from sysindexes where name='e_newsban_editionid')
    CREATE  INDEX [e_newsban_editionid] ON [dbo].[e_newsban]([editionid]) ON [PRIMARY]
GO
/*更新 editionid*/
     declare @newsid int,@editionids varchar(120)
     declare rs cursor for select  [id],editionid from e_news 
     open rs
     fetch next from rs into @newsid,@editionids
     while @@fetch_status=0
     begin         
         --here process
         --left(@newsid,charindex(',',@newsid)-1)
         insert into e_newsban (newsid,editionid) select @newsid,[Value] from [dbo].[SplitString](@editionids,',',1)
         fetch next from rs  into @newsid,@editionids
     end
     close rs
     deallocate rs
GO
/*drop editionid feild*/
alter  table [dbo].[e_news]  drop   column  editionid
GO
if not  exists(select * from sysindexes where name='e_news_qiid')
 CREATE  INDEX [e_news_qiid] ON [dbo].[e_news]([qiid]) ON [PRIMARY]
GO
if not  exists(select * from sysindexes where name='e_news_issuedate')
 CREATE  INDEX [e_news_issuedate] ON [dbo].[e_news]([issuedate]) ON [PRIMARY]
GO
if not  exists(select * from sysindexes where name='e_news_classid')
 CREATE  INDEX [e_news_classid] ON [dbo].[e_news]([classid]) ON [PRIMARY]
GO
/******mvc_info*****/
alter table [dbo].[mvc_info] ADD [jiantitle] [nvarchar] (200) COLLATE Chinese_PRC_CI_AS NULL
GO
UPDATE [dbo].[mvc_info] SET [jiantitle] =''
GO





