CRM announcement management structure

If exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [crm_bulletin] ') and objectproperty (ID, n' isusertable') = 1)
Drop table [DBO]. [crm_bulletin]
Go

If exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [crm_bulletin_type] ') and objectproperty (ID, n' isusertable') = 1)
Drop table [DBO]. [crm_bulletin_type]
Go

If exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [crm_bulletin_uploadfile] ') and objectproperty (ID, n'isusertable') = 1)
Drop table [DBO]. [crm_bulletin_uploadfile]
Go

Create Table [DBO]. [crm_bulletin] (
[ID] [numeric] (10, 0) Identity (1, 1) not null,
[Createdate] [datetime] Null,
[Sendertypeid] [varchar] (30) Collate chinese_prc_ci_as null,
[Sendername] [varchar] (40) Collate chinese_prc_ci_as null,
[Title] [varchar] (200) Collate chinese_prc_ci_as null,
[Content] [text] collate chinese_prc_ci_as null,
[Uploadfilenum] [varchar] (100) Collate chinese_prc_ci_as null,
[Isdel] [int] Null,
[Type] [char] (20) Collate chinese_prc_ci_as null
) On [primary] textimage_on [primary]
Go

Create Table [DBO]. [crm_bulletin_type] (
[ID] [int] identity (1, 1) not null,
[Bultext] [char] (20) Collate chinese_prc_ci_as null,
[Bulvale] [char] (20) Collate chinese_prc_ci_as null,
[Imgsrc] [char] (100) Collate chinese_prc_ci_as null
) On [primary]
Go

Create Table [DBO]. [crm_bulletin_uploadfile] (
[ID] [int] identity (1, 1) not null,
[Createdate] [datetime] not null,
[Creatortypeid] [varchar] (30) Collate chinese_prc_ci_as null,
[Filename] [varchar] (200) Collate chinese_prc_ci_as null,
[Contenttype] [varchar] (100) Collate chinese_prc_ci_as null,
[Size] [int] Null,
[Path] [varchar] (400) Collate chinese_prc_ci_as null,
[Uploadfilenum] [char] (100) Collate chinese_prc_ci_as null,
[Description] [char] (200) Collate chinese_prc_ci_as null
) On [primary]
Go

Alter table [DBO]. [crm_bulletin] With nocheck add
Constraint [pk_crm_bulletin] primary key clustered
(
[ID]
) On [primary]
Go

Alter table [DBO]. [crm_bulletin_type] With nocheck add
Constraint [pk_crm_bulletin_type] primary key clustered
(
[ID]
) On [primary]
Go

Alter table [DBO]. [crm_bulletin] add
Constraint [df_crm_bulletin_createdate] default (getdate () for [createdate],
Constraint [df_crm_bulletin_isdel] default (0) for [isdel]
Go

Alter table [DBO]. [crm_bulletin_uploadfile] add
Constraint [df_crm_bulletin_uploadfile_createdate] default (getdate () for [createdate]
Go

 

-- Stored Procedure
If exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [crm_buluploadfile_insert] ') and objectproperty (ID, n' isprocedure') = 1)
Drop procedure [DBO]. [crm_buluploadfile_insert]
Go

If exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [crm_bulletin_insert] ') and objectproperty (ID, n' isprocedure') = 1)
Drop procedure [DBO]. [crm_bulletin_insert]
Go

If exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [crm_bulletin_update] ') and objectproperty (ID, n' isprocedure') = 1)
Drop procedure [DBO]. [crm_bulletin_update]
Go

Set quoted_identifier on
Go
Set ansi_nulls on
Go

Create procedure crm_buluploadfile_insert
@ Creatortypeid varchar (30 ),
@ Filename varchar (200 ),
@ Contenttype varchar (100 ),
@ Size int,
@ Path varchar (400 ),
@ Uploadfilenum char (100)

As
Insert into crm_bulletin_uploadfile (
[Creatortypeid], [filename], [contenttype], [size], [path], [uploadfilenum]
) Values (
@ Creatortypeid, @ filename, @ contenttype, @ size, @ path, @ uploadfilenum
)
If @ error <> 0
Goto Error
Else
 
Goto success
Error: Select 'Statement error, error code: '+ @ error as info

Success: Select 'success' as info
Go
Set quoted_identifier off
Go
Set ansi_nulls on
Go

Set quoted_identifier on
Go
Set ansi_nulls on
Go

Create procedure crm_bulletin_insert
@ Sendertypeid varchar (30 ),
@ Sendername varchar (40 ),
@ Title varchar (200 ),
@ Content text,
@ Uploadfilenum varchar (100 ),
@ Type char (20)
As
Insert crm_bulletin (sendertypeid, sendername, title, content, uploadfilenum, type)
Values
(@ Sendertypeid, @ sendername, @ title, @ content, @ uploadfilenum, @ type)
If @ error <> 0
Goto Error
Else
Goto success
Return
Error: Select 'Statement error, error code: '+ @ error as info
 
Success: Select 'success' as info
Go
Set quoted_identifier off
Go
Set ansi_nulls on
Go

Set quoted_identifier on
Go
Set ansi_nulls on
Go

Create procedure crm_bulletin_update
(
@ ID numeric (10, 0 ),
@ Title varchar (200 ),
@ Content text,
@ Type char (20)
)
As
Set nocount on
Update crm_bulletin set
[Title] = @ title, [content] = @ content, [type] = @ Type
Where [ID] = @ ID
If @ error <> 0
Goto Error
Else
Goto success
Return
Error: Select 'Statement error, error code: '+ @ error as info

Success: Select 'success' as info
Go
Set quoted_identifier off
Go
Set ansi_nulls on
Go