Four stored procedures of Shopaholics

1. importdata

Set ansi_nulls on
Set quoted_identifier on
Go

Alter procedure [DBO]. [importdata] (
@ Table_type int
)
As
If @ table_type = 1
Begin
Insert into gwkproduct_one
Select [ID]
, [Merchantid]
, [Productname]
, [Refprice]
, [Price_1]
, [Pinpai]
, [Picurl]
, [Shortintro]
, [Searchkeyword]
, [Browsenodekeyword]
, [Url]
, Getdate () from gwkproduct g
Where [browsenodekeyword]! = ''And [browsenodekeyword] is not null
And [shortintro]! = ''And [shortintro] is not null
And G. Flag = 1
And [merchantid] In (select M. merchid from merchant m
Where
M. Status = 1)
-- Print @ Error
End
Else
Begin
Insert into gwkproduct_two
Select [ID]
, [Merchantid]
, [Productname]
, [Refprice]
, [Price_1]
, [Pinpai]
, [Picurl]
, [Shortintro]
, [Searchkeyword]
, [Browsenodekeyword]
, [Url]
, Getdate () from gwkproduct g
Where [browsenodekeyword]! = ''And [browsenodekeyword] is not null
And [shortintro]! = ''And [shortintro] is not null
And G. Flag = 1
And [merchantid] In (select M. merchid from merchant m
Where
M. Status = 1)
-- Print @ Error
End

 

 

2. switchandimport

Set ansi_nulls on
Set quoted_identifier on
Go

Alter procedure [DBO]. [switchandimport]
As
Declare
@ V_worktablename nvarchar (50 ),
@ V_startdatetime datetime,
@ V_worktablename_new nvarchar (50)

Declare cur_gwkproduct_switch cursor
For select worktablename, startdatetime
From gwkproduct_switch -- for update of worktablename, startdatetime
Open cur_gwkproduct_switch

Fetch next from cur_gwkproduct_switch into @ v_worktablename, @ v_startdatetime
-- Release cursor start
Close cur_gwkproduct_switch
Deallocate cur_gwkproduct_switch
-- Release cursor end

If @ v_worktablename! = 'Gwkproduct _ one'

Begin
------------- Imort data to gwkproduct_one start
Set @ v_worktablename_new = 'gwkproduct _ one'
Print @ v_worktablename_new
--------

Truncate table gwkproduct_one

Exec dropindexproduct 1
Exec importdata 1
Exec createindexproduct 1
--------- Fulltext rebuild
Exec sp_fulltext_catalog 'gouukeone', 'rebuilt'
---- Create Fulltext start

Exec sp_fulltext_catalog 'gouukeone ', 'start _ full'
While fulltextcatalogproperty ('gouukeone', 'populatestatus') <> 0
Begin
Waitfor delay '0: 2: 30'
End
---- Create Fulltext end
------------- Imort data to gwkproduct_one end
End

Else
 
Begin
------------- Imort data to gwkproduct_two start
Set @ v_worktablename_new = 'gwkproduct _ two'
Print @ v_worktablename_new
--------
Truncate table gwkproduct_two

Exec dropindexproduct 2
Exec importdata 2
Exec createindexproduct 2
--------- Fulltext rebuild
Exec sp_fulltext_catalog 'gouwuketwo ', 'rebuilt'
---- Create Fulltext start
Exec sp_fulltext_catalog 'gouwuketwo ', 'start _ full'
While fulltextcatalogproperty ('gouwuketwo ', 'populatestatus') <> 0
Begin
Waitfor delay '0: 2: 30'
End
---- Create Fulltext end
------------- Imort data to gwkproduct_two end
End

----------------------
Update gwkproduct_switch
Set worktablename = @ v_worktablename_new, startdatetime = dateadd (minute, 24*60 + 10, getdate ())
Where worktablename = @ v_worktablename

3. dropindexproduct

Set ansi_nulls on
Set quoted_identifier on
Go

Alter procedure [DBO]. [dropindexproduct] (
@ Table_type int
)
As
If @ table_type = 1
Begin
Drop index [ix_gwkproduct_id_bnk_pinpai] on [DBO]. [gwkproduct_one]

Drop index [ix_gwkproduct_id_price_1] on [DBO]. [gwkproduct_one]

Drop index [ix_id_bkn_price1] on [DBO]. [gwkproduct_one]

Drop index [ix_id_bnk_merchantid] on [DBO]. [gwkproduct_one]

Drop index [ix_id_bnk_merchantid_pinpai] on [DBO]. [gwkproduct_one]

Drop index [ix_id_bnk_merchantid_price] on [DBO]. [gwkproduct_one]

Drop index [ix_id_bnk_pin1__mchid_price1] on [DBO]. [gwkproduct_one]

Drop index [ix_id_pin1__bnk_merchantid] on [DBO]. [gwkproduct_one]

Drop index [ix_id_pin1__bnk_price1] on [DBO]. [gwkproduct_one]

Drop index [ix_id_price‑merchantid_bnk_pinpai] on [DBO]. [gwkproduct_one]
End

Else

Begin
Drop index [ix_gwkproduct_id_bnk_pinpai] on [DBO]. [gwkproduct_two]

Drop index [ix_gwkproduct_id_price_1] on [DBO]. [gwkproduct_two]

Drop index [ix_id_bkn_price1] on [DBO]. [gwkproduct_two]

Drop index [ix_id_bnk_merchantid] on [DBO]. [gwkproduct_two]

Drop index [ix_id_bnk_merchantid_pinpai] on [DBO]. [gwkproduct_two]

Drop index [ix_id_bnk_merchantid_price] on [DBO]. [gwkproduct_two]

Drop index [ix_id_bnk_pin1__mchid_price1] on [DBO]. [gwkproduct_two]

Drop index [ix_id_pin1__bnk_merchantid] on [DBO]. [gwkproduct_two]

Drop index [ix_id_pin1__bnk_price1] on [DBO]. [gwkproduct_two]

Drop index [ix_id_price‑merchantid_bnk_pinpai] on [DBO]. [gwkproduct_two]

End

 

4. createindexproduct

Set ansi_nulls on
Set quoted_identifier on
Go

Alter procedure [DBO]. [createindexproduct] (
@ Table_type int
)
As
If @ table_type = 1
Begin
--- Create gwkproduct_one index start
Create unique nonclustered index [ix_gwkproduct_id_bnk_pinpai] on [DBO]. [gwkproduct_one]
(
[ID] ASC,
[Browsenodekeyword] ASC,
[Pinpai] ASC
)

Create unique nonclustered index [ix_gwkproduct_id_price_1] on [DBO]. [gwkproduct_one]
(
[ID] ASC,
[Price_1] ASC
)

Create unique nonclustered index [ix_id_bkn_price1] on [DBO]. [gwkproduct_one]
(
[ID] ASC,
[Browsenodekeyword] ASC,
[Price_1] ASC
)

Create unique nonclustered index [ix_id_bnk_merchantid] on [DBO]. [gwkproduct_one]
(
[ID] ASC,
[Browsenodekeyword] ASC,
[Merchantid] ASC
)

Create unique nonclustered index [ix_id_bnk_merchantid_pinpai] on [DBO]. [gwkproduct_one]
(
[ID] ASC,
[Browsenodekeyword] ASC,
[Merchantid] ASC,
[Pinpai] ASC
)

Create unique nonclustered index [ix_id_bnk_merchantid_price] on [DBO]. [gwkproduct_one]
(
[ID] ASC,
[Browsenodekeyword] ASC,
[Merchantid] ASC,
[Price_1] ASC
)

Create unique nonclustered index [ix_id_bnk_pin1__mchid_price1] on [DBO]. [gwkproduct_one]
(
[ID] ASC,
[Browsenodekeyword] ASC,
[Pinpai] ASC,
[Merchantid] ASC,
[Price_1] ASC
)

Create unique nonclustered index [ix_id_pin1__bnk_merchantid] on [DBO]. [gwkproduct_one]
(
[ID] ASC,
[Pinpai] ASC,
[Browsenodekeyword] ASC,
[Merchantid] ASC
)

Create unique nonclustered index [ix_id_pin1__bnk_price1] on [DBO]. [gwkproduct_one]
(
[ID] ASC,
[Pinpai] ASC,
[Browsenodekeyword] ASC,
[Price_1] ASC
)

Create unique nonclustered index [ix_id_price‑merchantid_bnk_pinpai] on [DBO]. [gwkproduct_one]
(
[ID] ASC,
[Price_1] ASC,
[Merchantid] ASC,
[Browsenodekeyword] ASC,
[Pinpai] ASC
)
--- Create gwkproduct_one index end
End

Else

Begin
--- Create gwkproduct_two index start
Create unique nonclustered index [ix_gwkproduct_id_bnk_pinpai] on [DBO]. [gwkproduct_two]
(
[ID] ASC,
[Browsenodekeyword] ASC,
[Pinpai] ASC
)

Create unique nonclustered index [ix_gwkproduct_id_price_1] on [DBO]. [gwkproduct_two]
(
[ID] ASC,
[Price_1] ASC
)

Create unique nonclustered index [ix_id_bkn_price1] on [DBO]. [gwkproduct_two]
(
[ID] ASC,
[Browsenodekeyword] ASC,
[Price_1] ASC
)

Create unique nonclustered index [ix_id_bnk_merchantid] on [DBO]. [gwkproduct_two]
(
[ID] ASC,
[Browsenodekeyword] ASC,
[Merchantid] ASC
)

Create unique nonclustered index [ix_id_bnk_merchantid_pinpai] on [DBO]. [gwkproduct_two]
(
[ID] ASC,
[Browsenodekeyword] ASC,
[Merchantid] ASC,
[Pinpai] ASC
)

Create unique nonclustered index [ix_id_bnk_merchantid_price] on [DBO]. [gwkproduct_two]
(
[ID] ASC,
[Browsenodekeyword] ASC,
[Merchantid] ASC,
[Price_1] ASC
)

Create unique nonclustered index [ix_id_bnk_pin1__mchid_price1] on [DBO]. [gwkproduct_two]
(
[ID] ASC,
[Browsenodekeyword] ASC,
[Pinpai] ASC,
[Merchantid] ASC,
[Price_1] ASC
)

Create unique nonclustered index [ix_id_pin1__bnk_merchantid] on [DBO]. [gwkproduct_two]
(
[ID] ASC,
[Pinpai] ASC,
[Browsenodekeyword] ASC,
[Merchantid] ASC
)

Create unique nonclustered index [ix_id_pin1__bnk_price1] on [DBO]. [gwkproduct_two]
(
[ID] ASC,
[Pinpai] ASC,
[Browsenodekeyword] ASC,
[Price_1] ASC
)

Create unique nonclustered index [ix_id_price‑merchantid_bnk_pinpai] on [DBO]. [gwkproduct_two]
(
[ID] ASC,
[Price_1] ASC,
[Merchantid] ASC,
[Browsenodekeyword] ASC,
[Pinpai] ASC
)
--- Create gwkproduct_two index end
End