CREATE TABLE [REBLOGGER_KEYWORDS] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[keyword_display] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[keyword1] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[keyword2] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[keyword3] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[keyword4] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[keyword5] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[keyword6] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[priority] [int] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [REBLOGGER_POSTS] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[BLOG_ID] [int] NOT NULL ,
	[POST_PATH] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[POST_URL] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[POST_TITLE] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[POST] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[POST_DATE] [datetime] NOT NULL ,
	[POST_ACTIVE] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_REBLOG_POSTS_POST_ACTIVE] DEFAULT ('Y'),
	[keyword_id] [int] NULL ,
	CONSTRAINT [PK_REBLOG_POSTS] PRIMARY KEY  NONCLUSTERED 
	(
		[ID]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE procedure pr_REBLOGGER_RankPosts 
as

set nocount on
declare @mId int, @mKeyWord1 varchar(25),@mKeyWord2 varchar(25),@mKeyWord3 varchar(25),@mKeyWord4 varchar(25),@mKeyWord5 varchar(25),@mKeyWord6 varchar(25)
declare @updSQL nvarchar(4000)
declare rate_crsr CURSOR FAST_FORWARD FOR select id, keyword1, keyword2, keyword3, keyword4, keyword5, keyword6 from reblogger_keywords order by priority

/*open cursor*/
open rate_crsr

/* get first row from cursor to start with*/

fetch next from rate_crsr into @mid, @mKeyWord1, @mKeyWord2, @mKeyWord3, @mKeyWord4, @mKeyWord5,@mKeyWord6

/* loop through all records fetching one row at a time*/
set @updSQL = 'UPDATE REBLOGGER_POSTS SET KEYWORD_id = NULL'

exec sp_executesql @updSQL

while @@fetch_status = 0
begin
	/* update reblogger_posts for matching keyword in the post title*/
	if len(@mkeyword1) > 0
	begin
		set @updSQL = 'UPDATE REBLOGGER_POSTS SET KEYWORD_id = '''  +  cast(@mid as varchar(10)) + ''' where post_title like ''%' + @mkeyword1 + '%'' and KEYWORD_ID is null'
		/*select @updsql*/
		exec sp_executesql @updSQL
		
		/* update reblogger_posts for matching keyword in the post body*/
		set @updSQL = 'UPDATE REBLOGGER_POSTS SET KEYWORD_ID = '''  +  cast(@mid as varchar(10)) + ''' where post like ''%' + @mkeyword1 + '%'' and KEYWORD_ID is null'
		
		/*select @updsql*/
		exec sp_executesql @updSQL
	end
	if len(@mkeyword2) > 0
	begin
		set @updSQL = 'UPDATE REBLOGGER_POSTS SET KEYWORD_id = '''  +  cast(@mid as varchar(10)) + ''' where post_title like ''%' + @mkeyword2 + '%'' and KEYWORD_ID is null'
		/*select @updsql*/
		exec sp_executesql @updSQL

		/* update reblogger_posts for matching keyword in the post body*/

		set @updSQL = 'UPDATE REBLOGGER_POSTS SET KEYWORD_ID = '''  +  cast(@mid as varchar(10)) + ''' where post like ''%' + @mkeyword2 + '%'' and KEYWORD_ID is null'
		/*select @updsql*/
		exec sp_executesql @updSQL
	end
	if len(@mkeyword3) > 0
	begin
		set @updSQL = 'UPDATE REBLOGGER_POSTS SET KEYWORD_id = '''  +  cast(@mid as varchar(10)) + ''' where post_title like ''%' + @mkeyword3 + '%'' and KEYWORD_ID is null'
		/*select @updsql*/
		exec sp_executesql @updSQL

		/* update reblogger_posts for matching keyword in the post body*/
		set @updSQL = 'UPDATE REBLOGGER_POSTS SET KEYWORD_ID = '''  +  cast(@mid as varchar(10)) + ''' where post like ''%' + @mkeyword3 + '%'' and KEYWORD_ID is null'
		/*select @updsql*/
		exec sp_executesql @updSQL
	end

	if len(@mkeyword4) > 0
	begin
		set @updSQL = 'UPDATE REBLOGGER_POSTS SET KEYWORD_id = '''  +  cast(@mid as varchar(10)) + ''' where post_title like ''%' + @mkeyword4 + '%'' and KEYWORD_ID is null'
		/*select @updsql*/
		exec sp_executesql @updSQL

		/* update reblogger_posts for matching keyword in the post body*/
		set @updSQL = 'UPDATE REBLOGGER_POSTS SET KEYWORD_ID = '''  +  cast(@mid as varchar(10)) + ''' where post like ''%' + @mkeyword4 + '%'' and KEYWORD_ID is null'
		/*select @updsql*/
		exec sp_executesql @updSQL
	end

	if len(@mkeyword5) > 0
	begin
		set @updSQL = 'UPDATE REBLOGGER_POSTS SET KEYWORD_id = '''  +  cast(@mid as varchar(10)) + ''' where post_title like ''%' + @mkeyword5 + '%'' and KEYWORD_ID is null'
		/*select @updsql*/
		exec sp_executesql @updSQL

		/* update reblogger_posts for matching keyword in the post body*/
		set @updSQL = 'UPDATE REBLOGGER_POSTS SET KEYWORD_ID = '''  +  cast(@mid as varchar(10)) + ''' where post like ''%' + @mkeyword5 + '%'' and KEYWORD_ID is null'
		/*select @updsql*/
		exec sp_executesql @updSQL
	end

	if len(@mkeyword6) > 0
	begin
		set @updSQL = 'UPDATE REBLOGGER_POSTS SET KEYWORD_id = '''  +  cast(@mid as varchar(10)) + ''' where post_title like ''%' + @mkeyword6 + '%'' and KEYWORD_ID is null'
		/*select @updsql*/
		exec sp_executesql @updSQL

		/* update reblogger_posts for matching keyword in the post body*/
		set @updSQL = 'UPDATE REBLOGGER_POSTS SET KEYWORD_ID = '''  +  cast(@mid as varchar(10)) + ''' where post like ''%' + @mkeyword6 + '%'' and KEYWORD_ID is null'
		/*select @updsql*/
		exec sp_executesql @updSQL
	end

	/* fetch next record from cursor */
	fetch next from rate_crsr into @mid, @mKeyWord1, @mKeyWord2, @mKeyWord3, @mKeyWord4, @mKeyWord5, @mKeyWord6
end
/* mark post for deletion if no matching keyword found */
set @updSQL = 'UPDATE REBLOGGER_POSTS SET POST_ACTIVE = ''' + 'D' + ''' where KEYWORD_ID is null'
exec sp_executesql @updSQL
set @updSQL = 'UPDATE REBLOGGER_POSTS SET POST_ACTIVE = ''' + 'Y' + ''' where KEYWORD_ID is NOT null'
exec sp_executesql @updSQL
/*select @updsql*/

/* close cursor*/
deallocate rate_crsr

quote:

---

Is it too late to change your design ?

---

quote:

---

Furthermore how do I make the post table faster?

---