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
--存储过程
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 '语句发生错语,错语代码:'+@@ERROR as info
SUCCESS: SELECT '成功' 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 '语句发生错语,错语代码:'+@@ERROR as info
SUCCESS: SELECT '成功' 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 '语句发生错语,错语代码:'+@@ERROR as info
SUCCESS: SELECT '成功' as info
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO