系统架构划设想计之,怎么样根据一定法规自动

---摇奖observeh数据库设计  Function getSpace  lottery
/*
-- Author:geovindu 涂聚文
-- Date: 20180427 为了自写生成代码。根据Metedata 生成有注释和关系表的代码,表必须要主键和注释
CREATE DATABASE LotteryDrawing
GO

USE LotteryDrawing
GO
*/
---獎項項目表AwardProject
IF EXISTS (select * from sysobjects where id = object_id(N'dbo.AwardProject') and OBJECTPROPERTY(id, N'IsUserTable') = 1) 
DROP TABLE AwardProject 
GO
CREATE TABLE AwardProject
(
 ProjectId INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
 ProjectName NVARCHAR(300) NOT NULL,   --項目名稱,顯示年度年會上
 ProjectYear VARCHAR(20) NOT NULL,   --年度名稱
 ProjectDate DATETIME DEFAULT(GETDATE())  --創建日期 
)
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'AwardProject', null,null))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'獎項項目表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AwardProject', @level2type=NULL,@level2name=NULL
GO
--列描述
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'項目ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AwardProject', @level2type=N'COLUMN',@level2name=N'ProjectId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'項目名稱,顯示年度年會上' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AwardProject', @level2type=N'COLUMN',@level2name=N'ProjectName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'年度名稱' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AwardProject', @level2type=N'COLUMN',@level2name=N'ProjectYear'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'創建日期' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AwardProject', @level2type=N'COLUMN',@level2name=N'ProjectDate'
GO

SELECT * FROM dbo.AwardProject
GO


--抽獎員工表 Employee
IF EXISTS (select * from sysobjects where id = object_id(N'dbo.Employee') and OBJECTPROPERTY(id, N'IsUserTable') = 1) 
DROP TABLE Employee 
GO
CREATE TABLE Employee
(
 EmployeeId INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
 EmployeeProjectId INT 
  FOREIGN KEY REFERENCES AwardProject(ProjectId),  --外鍵,項目ID
 EmployeeNO VARCHAR(30) NOT NULL,    --員工編號
 EmployeeName NVARCHAR(100) NOT NULL, --員工姓名
 EmployeeMobile VARCHAR(20) NOT NULL, --手機號碼
           --skype
 EmployeeDate DATETIME DEFAULT(GETDATE())
)
GO

---表描述
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'TicketManage', null,null))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'抽獎員工表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Employee', @level2type=NULL,@level2name=NULL
GO
--列描述
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'員工ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Employee', @level2type=N'COLUMN',@level2name=N'EmployeeId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'外鍵,項目ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Employee', @level2type=N'COLUMN',@level2name=N'EmployeeProjectId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'員工編號' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Employee', @level2type=N'COLUMN',@level2name=N'EmployeeNO'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'員工姓名' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Employee', @level2type=N'COLUMN',@level2name=N'EmployeeName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'手機號碼' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Employee', @level2type=N'COLUMN',@level2name=N'EmployeeMobile'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'日期' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Employee', @level2type=N'COLUMN',@level2name=N'EmployeeDate'
GO

SELECT * FROM dbo.Employee
GO



--奖项表 AwardType
IF EXISTS (select * from sysobjects where id = object_id(N'dbo.AwardType') and OBJECTPROPERTY(id, N'IsUserTable') = 1) 
DROP TABLE AwardType 
GO
CREATE TABLE AwardType
(
 AwardTypeId INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
 AwardProjectId INT 
  FOREIGN KEY REFERENCES AwardProject(ProjectId),  --外鍵,項目ID
 AwardName NVARCHAR(100) NOT NULL,      --獎項名稱,如:特等獎,一等獎
 AwardPersonNumber INT DEFAULT(1),        --獎項人數
 AwardVeryNumber INT DEFAULT(1),       --每次抽的人數
 AwardVeryProduct INT DEFAULT(1)       --獎項每人產品數量 

)
GO
---表描述
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'TicketManage', null,null))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'奖项表 ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AwardType', @level2type=NULL,@level2name=NULL
GO
--列描述
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'奖项ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AwardType', @level2type=N'COLUMN',@level2name=N'AwardTypeId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'外鍵,項目ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AwardType', @level2type=N'COLUMN',@level2name=N'AwardProjectId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'獎項名稱,如:特等獎,一等獎' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AwardType', @level2type=N'COLUMN',@level2name=N'AwardName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'獎項人數' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AwardType', @level2type=N'COLUMN',@level2name=N'AwardPersonNumber'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'每次抽的人數' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AwardType', @level2type=N'COLUMN',@level2name=N'AwardVeryNumber'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'獎項每人產品數量' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AwardType', @level2type=N'COLUMN',@level2name=N'AwardVeryProduct'
GO





SELECT * FROM dbo.AwardType
GO


--奖品表 AwardProduct
IF EXISTS (select * from sysobjects where id = object_id(N'dbo.AwardProduct') and OBJECTPROPERTY(id, N'IsUserTable') = 1) 
DROP TABLE AwardProduct 
GO
CREATE TABLE AwardProduct
(
 ProductId INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
 ProductAwardTypeId INT
  FOREIGN KEY REFERENCES AwardType(AwardTypeId),   --外鍵,獎項類型ID
 ProductName NVARCHAR(200) NOT NULL,       --獎品名稱
 ProductNo VARCHAR(50) NULL,         --獎品編號(便于掃碼)
 ProductTotal INT NOT NULL         --獎品數量 (總數量要和獎品人數*每個獎項數量相符,程序要驗證)
)
GO


---表描述
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'AwardProduct', null,null))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'奖品表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AwardProduct', @level2type=NULL,@level2name=NULL
GO
--列描述
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'獎品ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AwardProduct', @level2type=N'COLUMN',@level2name=N'ProductId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'外鍵,獎項類型ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AwardProduct', @level2type=N'COLUMN',@level2name=N'ProductAwardTypeId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'獎品名稱' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AwardProduct', @level2type=N'COLUMN',@level2name=N'ProductName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'獎品編號' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AwardProduct', @level2type=N'COLUMN',@level2name=N'ProductNo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'獎品數量' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AwardProduct', @level2type=N'COLUMN',@level2name=N'ProductTotal'
GO

SELECT * FROM dbo.AwardProduct
GO



--獎品圖片表
IF EXISTS (select * from sysobjects where id = object_id(N'dbo.AwardProductImage') and OBJECTPROPERTY(id, N'IsUserTable') = 1) 
DROP TABLE AwardProductImage 
GO
CREATE TABLE AwardProductImage
(
 ProductImageId INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
 ProductImageProductId INT
  FOREIGN KEY REFERENCES AwardProduct(ProductId),   --外鍵,產品ID
 ProductImageByte IMAGE          --產品圖片   
)
GO

---表描述
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'AwardProductImage', null,null))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'獎品圖片表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AwardProductImage', @level2type=NULL,@level2name=NULL
GO
--列描述
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'圖片ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AwardProductImage', @level2type=N'COLUMN',@level2name=N'ProductImageId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'外鍵,產品ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AwardProductImage', @level2type=N'COLUMN',@level2name=N'ProductImageProductId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'產品圖片' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AwardProductImage', @level2type=N'COLUMN',@level2name=N'ProductImageByte'
GO

SELECT * FROM dbo.AwardProductImage
GO


--获奖者列表 LuckyWinner  ---獎品确認收簽
IF EXISTS (select * from sysobjects where id = object_id(N'dbo.LuckyWinner') and OBJECTPROPERTY(id, N'IsUserTable') = 1) 
DROP TABLE LuckyWinner 
GO
CREATE TABLE LuckyWinner
(
 WinnerId INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
 WinnerEmployeeId INT 
  FOREIGN KEY REFERENCES Employee(EmployeeId),  --外鍵,員工ID 
 WinnerAwardTypeId INT
  FOREIGN KEY REFERENCES AwardType(AwardTypeId),  --外鍵,獎項類型ID 
 WinnerIsOk BIT DEFAULT(0),        --是否簽收确認
 WinnerSinger NVARCHAR(50) NULL,       --簽收人(或代理人)
 WinnerDate datetime NULL        --确認簽收日期  
)
GO


---表描述
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'LuckyWinner', null,null))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'获奖者列表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'LuckyWinner', @level2type=NULL,@level2name=NULL
GO
--列描述
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'获奖者ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'LuckyWinner', @level2type=N'COLUMN',@level2name=N'WinnerId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'外鍵,員工ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'LuckyWinner', @level2type=N'COLUMN',@level2name=N'WinnerEmployeeId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'外鍵,獎項類型ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'LuckyWinner', @level2type=N'COLUMN',@level2name=N'WinnerAwardTypeId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否簽收确認' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'LuckyWinner', @level2type=N'COLUMN',@level2name=N'WinnerIsOk'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'簽收人(或代理人)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'LuckyWinner', @level2type=N'COLUMN',@level2name=N'WinnerSinger'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'确認簽收日期' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'LuckyWinner', @level2type=N'COLUMN',@level2name=N'WinnerDate'
GO




SELECT * FROM dbo.LuckyWinner
GO


--所穫獎者發短信,SKYPE信息等 WinnerSms
IF EXISTS (select * from sysobjects where id = object_id(N'dbo.WinnerSms') and OBJECTPROPERTY(id, N'IsUserTable') = 1) 
DROP TABLE WinnerSms 
GO
CREATE TABLE WinnerSms
(
 SmsId INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
 SmsEmployeeId INT 
  FOREIGN KEY REFERENCES Employee(EmployeeId),  --外鍵,員工ID
 SmsContent NVARCHAR(2000) NOT NULL,      --短信內容
 SmsDate DATETIME DEFAULT(GETDATE())      --發送時間 
)
GO
---表描述
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'WinnerSms', null,null))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'所穫獎者發短信表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'WinnerSms', @level2type=NULL,@level2name=NULL
GO
--列描述
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'短信ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'WinnerSms', @level2type=N'COLUMN',@level2name=N'SmsId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'外鍵,員工ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'WinnerSms', @level2type=N'COLUMN',@level2name=N'SmsEmployeeId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'短信內容' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'WinnerSms', @level2type=N'COLUMN',@level2name=N'SmsContent'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'發送時間' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'WinnerSms', @level2type=N'COLUMN',@level2name=N'SmsDate'
GO

SELECT * FROM dbo.WinnerSms
GO

--窗口背景圖
IF EXISTS (select * from sysobjects where id = object_id(N'dbo.FormBackground') and OBJECTPROPERTY(id, N'IsUserTable') = 1) 
DROP TABLE FormBackground 
GO
CREATE TABLE FormBackground
(
 BackgroundId INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
 BackgroundImage IMAGE,
 BackgroundFmName NVARCHAR(200)
)
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'FormBackground', null,null))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'窗口背景圖表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FormBackground', @level2type=NULL,@level2name=NULL
GO
--列描述
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FormBackground', @level2type=N'COLUMN',@level2name=N'BackgroundId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'背景圖' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FormBackground', @level2type=N'COLUMN',@level2name=N'BackgroundImage'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'窗口名稱' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'FormBackground', @level2type=N'COLUMN',@level2name=N'BackgroundFmName'
GO


SELECT * FROM dbo.FormBackground

问题

在Oracle一张表中插入一行数据,这里主键是自动生成的,何况依旧使用了Oracle主键ID插入时自动增长主意自动生成插入主键的。

软件设计阶段,个中二个要思虑的主题材料正是数据库主健,不思索无需主健的情事,本文首要关切于主健设计采用。

怎么样依照一定准则自动生成系列号码
要落到实处依据一定准则自动生成种类号,举例:6666四千0001,666650000002,666640000003等,这一个字段不是表的主键,算是属于工作对应得叁个字段,最初怀想直接查数据库,每一回活动加一,但这么在产出时就能够有标题;若加上synchronized载这段代码上,但在并发大的时候会不会对表发生死锁呢。不晓得我们有未有更加好的法子,分享一下呢。多谢啦。

  依据上述的代码生成的表,自动生成的大约的拉长、删除、修改、查询的存放进度(关联还尚无设想,可以虚构进来)

原因

计划数据的时候,自动队列生成主键,与原有表中历史数据中的主键冲突。

1. Identity  用作主健。

IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Insert_AwardProject')
DROP PROCEDURE dbo.proc_Insert_AwardProject
GO
CREATE PROCEDURE dbo.proc_Insert_AwardProject
(
 @ProjectName NVarChar(600),
 @ProjectYear VarChar(20),
 @ProjectDate DateTime
)
AS
INSERT INTO dbo.AwardProject
(
 [ProjectName] ,
 [ProjectYear] ,
 [ProjectDate]
)
 VALUES
(
 @ProjectName ,
 @ProjectYear ,
 @ProjectDate
)
GO

EXECUTE sp_addextendedproperty N'MS_Description', '獎項項目表增加记录存储过程', N'user', N'dbo', N'procedure', N'proc_Insert_AwardProject', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '項目名稱,顯示年度年會上', N'user', N'dbo', N'procedure', N'proc_Insert_AwardProject', N'parameter', N'@ProjectName'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '年度名稱', N'user', N'dbo', N'procedure', N'proc_Insert_AwardProject', N'parameter', N'@ProjectYear'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '創建日期', N'user', N'dbo', N'procedure', N'proc_Insert_AwardProject', N'parameter', N'@ProjectDate'
GO


IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Insert_AwardProjectOutput')
DROP PROCEDURE dbo.proc_Insert_AwardProjectOutput
GO
CREATE PROCEDURE dbo.proc_Insert_AwardProjectOutput
(
 @ProjectName NVarChar(600),
 @ProjectYear VarChar(20),
 @ProjectDate DateTime,
 @ProjectId int  output
)
AS
INSERT INTO dbo.AwardProject
(
 [ProjectName] ,
 [ProjectYear] ,
 [ProjectDate]
)
 VALUES
(
 @ProjectName ,
 @ProjectYear ,
 @ProjectDate
)
select @ProjectId=@@IDENTITY
GO

EXECUTE sp_addextendedproperty N'MS_Description', '獎項項目表添加记录有返回值的存储过程', N'user', N'dbo', N'procedure', N'proc_Insert_AwardProject', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '項目名稱,顯示年度年會上', N'user', N'dbo', N'procedure', N'proc_Insert_AwardProject', N'parameter', N'@ProjectName'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '年度名稱', N'user', N'dbo', N'procedure', N'proc_Insert_AwardProject', N'parameter', N'@ProjectYear'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '創建日期', N'user', N'dbo', N'procedure', N'proc_Insert_AwardProject', N'parameter', N'@ProjectDate'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '獎項項目表主键输出', N'user', N'dbo', N'procedure', N'proc_Insert_AwardProject', N'parameter', N'@ProjectId'
GO

IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Update_AwardProject')
DROP PROCEDURE dbo.proc_Update_AwardProject
GO
CREATE PROCEDURE dbo.proc_Update_AwardProject
(
 @ProjectId Int,
 @ProjectName NVarChar(600),
 @ProjectYear VarChar(20),
 @ProjectDate DateTime
)
AS
UPDATE dbo.AwardProject
 SET
  [ProjectName]=@ProjectName ,
  [ProjectYear]=@ProjectYear ,
  [ProjectDate]=@ProjectDate
 where
  [ProjectId]=@ProjectId
GO

EXECUTE sp_addextendedproperty N'MS_Description', '獎項項目表修改记录的存储过程', N'user', N'dbo', N'procedure', N'proc_Update_AwardProject', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '項目名稱,顯示年度年會上', N'user', N'dbo', N'procedure', N'proc_Update_AwardProject', N'parameter', N'@ProjectName'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '年度名稱', N'user', N'dbo', N'procedure', N'proc_Update_AwardProject', N'parameter', N'@ProjectYear'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '創建日期', N'user', N'dbo', N'procedure', N'proc_Update_AwardProject', N'parameter', N'@ProjectDate'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '獎項項目表主键', N'user', N'dbo', N'procedure', N'proc_Update_AwardProject', N'parameter', N'@ProjectId'
GO

IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Update_AwardProjectField')
DROP PROCEDURE dbo.proc_Update_AwardProjectField
GO
CREATE PROCEDURE dbo.proc_Update_AwardProjectField
(
 @ProjectId Int,
 @FieldValue NVARCHAR(1500)
)
AS
DECLARE @sql NVARCHAR(4000)
SET @sql='update  dbo.AwardProject set ' @FieldValue ' WHERE ProjectId ='  CAST(@ProjectId AS VARCHAR(50))
EXEC(@sql)
GO

EXECUTE sp_addextendedproperty N'MS_Description', '獎項項目表修改某一列数据的存储过程', N'user', N'dbo', N'procedure', N'proc_Update_AwardProjectField', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '獎項項目表修改字段名及其值如:[Remark]=A', N'user', N'dbo', N'procedure', N'proc_Update_AwardProjectField', N'parameter', N'@FieldValue'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '獎項項目表主键', N'user', N'dbo', N'procedure', N'proc_Update_AwardProjectField', N'parameter', N'@ProjectId'
GO

IF EXISTS (select * from sysobjects where [name] = 'dbo.proc_Delete_AwardProject')
DROP PROCEDURE dbo.proc_Delete_AwardProject
GO
CREATE PROCEDURE dbo.proc_Delete_AwardProject
(
 @ProjectId Int
)
as
DELETE
 dbo.AwardProject
 WHERE
  ProjectId = @ProjectId
GO

EXECUTE sp_addextendedproperty N'MS_Description', '獎項項目表删除记录的存储过程', N'user', N'dbo', N'procedure', N'proc_Delete_AwardProject', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '獎項項目表主键', N'user', N'dbo', N'procedure', N'proc_Delete_AwardProject', N'parameter', N'@ProjectId'
GO

IF EXISTS (select * from sysobjects where [name] = 'dbo.proc_Delete_AwardProjectId')
DROP PROCEDURE dbo.proc_Delete_AwardProjectId
GO
CREATE PROCEDURE dbo.proc_Delete_AwardProjectId
(
 @ProjectId varchar(2000)
)
as
DECLARE @strsql varchar(3000)
SET @strsql='DELETE dbo.AwardProject WHERE ProjectId in(' @ProjectId ')'
EXEC(@strsql)
GO

EXECUTE sp_addextendedproperty N'MS_Description', '獎項項目表删除多条记录的存储过程', N'user', N'dbo', N'procedure', N'proc_Delete_AwardProjectId', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '獎項項目表主键', N'user', N'dbo', N'procedure', N'proc_Delete_AwardProjectId', N'parameter', N'@ProjectId'
GO

IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Select_AwardProject')
DROP PROCEDURE dbo.proc_Select_AwardProject
GO
CREATE PROCEDURE dbo.proc_Select_AwardProject
(
 @ProjectId Int
)
AS
SELECT * FROM dbo.AwardProject WHERE ProjectId = @ProjectId
GO

EXECUTE sp_addextendedproperty N'MS_Description', '獎項項目表查询记录的存储过程', N'user', N'dbo', N'procedure', N'proc_Select_AwardProject', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '獎項項目表主键', N'user', N'dbo', N'procedure', N'proc_Select_AwardProject', N'parameter', N'@ProjectId'
GO

IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Select_AwardProjectExists')
DROP PROCEDURE dbo.proc_Select_AwardProjectExists
GO
CREATE PROCEDURE dbo.proc_Select_AwardProjectExists
(
 @ProjectId Int
)
AS
SELECT count(1) as H FROM dbo.AwardProject WHERE ProjectId = @ProjectId
GO

EXECUTE sp_addextendedproperty N'MS_Description', '獎項項目表是否存在某记录的存储过程', N'user', N'dbo', N'procedure', N'proc_Select_AwardProjectExists', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '獎項項目表主键', N'user', N'dbo', N'procedure', N'proc_Select_AwardProjectExists', N'parameter', N'@ProjectId'
GO

IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Select_AwardProjectCount')
DROP PROCEDURE dbo.proc_Select_AwardProjectCount
GO
CREATE PROCEDURE dbo.proc_Select_AwardProjectCount
(
 @where NVARCHAR(1000)
)
AS
DECLARE @sql NVARCHAR(4000)
SET @sql='select count(*) as H from dbo.AwardProject '
IF @where<>''
 SET @sql=@sql @where
EXEC(@sql)
GO

EXECUTE sp_addextendedproperty N'MS_Description', '獎項項目表共有多少条记录的存储过程', N'user', N'dbo', N'procedure', N'proc_Select_AwardProjectCount', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '獎項項目表要模糊查询的字段条件', N'user', N'dbo', N'procedure', N'proc_Select_AwardProjectCount', N'parameter', N'@where'
GO

IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Select_AwardProjectCountView')
DROP PROCEDURE dbo.proc_Select_AwardProjectCountView
GO
CREATE PROCEDURE dbo.proc_Select_AwardProjectCountView
(
 @where NVARCHAR(1000)
)
AS
DECLARE @sql NVARCHAR(4000)
SET @sql='select count(*) as H from dbo.View_AwardProject '
IF @where<>''
 SET @sql=@sql @where
EXEC(@sql)
GO

EXECUTE sp_addextendedproperty N'MS_Description', '獎項項目表视图查询共有多少条记录的存储过程', N'user', N'dbo', N'procedure', N'proc_Select_AwardProjectCountView', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '獎項項目表要模糊查询的字段条件', N'user', N'dbo', N'procedure', N'proc_Select_AwardProjectCountView', N'parameter', N'@where'
GO

IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Select_AwardProjectTitle')
DROP PROCEDURE dbo.proc_Select_AwardProjectTitle
GO
CREATE PROCEDURE dbo.proc_Select_AwardProjectTitle
(
 @FieldName NVARCHAR(100),
 @ProjectId Int
)
AS
DECLARE @sql NVARCHAR(4000)
SET @sql='select top 1 ' @FieldName ' from dbo.AwardProject WHERE ProjectId ='  CAST(@ProjectId AS VARCHAR(50))
EXEC(@sql)
GO

EXECUTE sp_addextendedproperty N'MS_Description', '獎項項目表返回某字段的标题记录的存储过程', N'user', N'dbo', N'procedure', N'proc_Select_AwardProjectTitle', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '獎項項目表要显示的字段名的列表', N'user', N'dbo', N'procedure', N'proc_Select_AwardProjectTitle', N'parameter', N'@FieldName'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '獎項項目表主键', N'user', N'dbo', N'procedure', N'proc_Select_AwardProjectTitle', N'parameter', N'@ProjectId'
GO

IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Select_AwardProjectFuzzySearch')
DROP PROCEDURE dbo.proc_Select_AwardProjectFuzzySearch
GO
CREATE PROCEDURE dbo.proc_Select_AwardProjectFuzzySearch
(
 @FieldList NVARCHAR(1000),
 @where NVARCHAR(2000)
)
AS
DECLARE @sql NVARCHAR(4000)
IF(@where<>'')
 SET @where=' WHERE ' @where 
IF(@FieldList='')
 SET @FieldList=' * ' 
SET @sql='select ' @FieldList ' from dbo.AwardProject ' @where
EXEC(@sql)
GO

EXECUTE sp_addextendedproperty N'MS_Description', '獎項項目表模糊查询记录的存储过程', N'user', N'dbo', N'procedure', N'proc_Select_AwardProject', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '獎項項目表要显示的字段名的列表', N'user', N'dbo', N'procedure', N'proc_Select_AwardProject', N'parameter', N'@FieldList'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '獎項項目表要模糊查询的字段条件', N'user', N'dbo', N'procedure', N'proc_Select_AwardProject', N'parameter', N'@where'
GO

IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Select_AwardProjectAll')
DROP PROCEDURE dbo.proc_Select_AwardProjectAll
GO
CREATE PROCEDURE dbo.proc_Select_AwardProjectAll
AS
SELECT * FROM dbo.AwardProject
GO

EXECUTE sp_addextendedproperty N'MS_Description', '獎項項目表查询所有记录存储过程', N'user', N'dbo', N'procedure', N'proc_Select_AwardProjectAll', NULL, NULL
GO


IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Insert_Employee')
DROP PROCEDURE dbo.proc_Insert_Employee
GO
CREATE PROCEDURE dbo.proc_Insert_Employee
(
 @EmployeeProjectId Int,
 @EmployeeNO VarChar(30),
 @EmployeeName NVarChar(200),
 @EmployeeMobile VarChar(20),
 @EmployeeDate DateTime
)
AS
INSERT INTO dbo.Employee
(
 [EmployeeProjectId] ,
 [EmployeeNO] ,
 [EmployeeName] ,
 [EmployeeMobile] ,
 [EmployeeDate]
)
 VALUES
(
 @EmployeeProjectId ,
 @EmployeeNO ,
 @EmployeeName ,
 @EmployeeMobile ,
 @EmployeeDate
)
GO

EXECUTE sp_addextendedproperty N'MS_Description', '抽獎員工表增加记录存储过程', N'user', N'dbo', N'procedure', N'proc_Insert_Employee', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '外鍵,項目ID', N'user', N'dbo', N'procedure', N'proc_Insert_Employee', N'parameter', N'@EmployeeProjectId'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '員工編號', N'user', N'dbo', N'procedure', N'proc_Insert_Employee', N'parameter', N'@EmployeeNO'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '員工姓名', N'user', N'dbo', N'procedure', N'proc_Insert_Employee', N'parameter', N'@EmployeeName'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '手機號碼', N'user', N'dbo', N'procedure', N'proc_Insert_Employee', N'parameter', N'@EmployeeMobile'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '日期', N'user', N'dbo', N'procedure', N'proc_Insert_Employee', N'parameter', N'@EmployeeDate'
GO


IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Insert_EmployeeOutput')
DROP PROCEDURE dbo.proc_Insert_EmployeeOutput
GO
CREATE PROCEDURE dbo.proc_Insert_EmployeeOutput
(
 @EmployeeProjectId Int,
 @EmployeeNO VarChar(30),
 @EmployeeName NVarChar(200),
 @EmployeeMobile VarChar(20),
 @EmployeeDate DateTime,
 @EmployeeId int  output
)
AS
INSERT INTO dbo.Employee
(
 [EmployeeProjectId] ,
 [EmployeeNO] ,
 [EmployeeName] ,
 [EmployeeMobile] ,
 [EmployeeDate]
)
 VALUES
(
 @EmployeeProjectId ,
 @EmployeeNO ,
 @EmployeeName ,
 @EmployeeMobile ,
 @EmployeeDate
)
select @EmployeeId=@@IDENTITY
GO

EXECUTE sp_addextendedproperty N'MS_Description', '抽獎員工表添加记录有返回值的存储过程', N'user', N'dbo', N'procedure', N'proc_Insert_Employee', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '外鍵,項目ID', N'user', N'dbo', N'procedure', N'proc_Insert_Employee', N'parameter', N'@EmployeeProjectId'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '員工編號', N'user', N'dbo', N'procedure', N'proc_Insert_Employee', N'parameter', N'@EmployeeNO'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '員工姓名', N'user', N'dbo', N'procedure', N'proc_Insert_Employee', N'parameter', N'@EmployeeName'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '手機號碼', N'user', N'dbo', N'procedure', N'proc_Insert_Employee', N'parameter', N'@EmployeeMobile'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '日期', N'user', N'dbo', N'procedure', N'proc_Insert_Employee', N'parameter', N'@EmployeeDate'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '抽獎員工表主键输出', N'user', N'dbo', N'procedure', N'proc_Insert_Employee', N'parameter', N'@EmployeeId'
GO

IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Update_Employee')
DROP PROCEDURE dbo.proc_Update_Employee
GO
CREATE PROCEDURE dbo.proc_Update_Employee
(
 @EmployeeId Int,
 @EmployeeProjectId Int,
 @EmployeeNO VarChar(30),
 @EmployeeName NVarChar(200),
 @EmployeeMobile VarChar(20),
 @EmployeeDate DateTime
)
AS
UPDATE dbo.Employee
 SET
  [EmployeeProjectId]=@EmployeeProjectId ,
  [EmployeeNO]=@EmployeeNO ,
  [EmployeeName]=@EmployeeName ,
  [EmployeeMobile]=@EmployeeMobile ,
  [EmployeeDate]=@EmployeeDate
 where
  [EmployeeId]=@EmployeeId
GO

EXECUTE sp_addextendedproperty N'MS_Description', '抽獎員工表修改记录的存储过程', N'user', N'dbo', N'procedure', N'proc_Update_Employee', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '外鍵,項目ID', N'user', N'dbo', N'procedure', N'proc_Update_Employee', N'parameter', N'@EmployeeProjectId'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '員工編號', N'user', N'dbo', N'procedure', N'proc_Update_Employee', N'parameter', N'@EmployeeNO'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '員工姓名', N'user', N'dbo', N'procedure', N'proc_Update_Employee', N'parameter', N'@EmployeeName'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '手機號碼', N'user', N'dbo', N'procedure', N'proc_Update_Employee', N'parameter', N'@EmployeeMobile'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '日期', N'user', N'dbo', N'procedure', N'proc_Update_Employee', N'parameter', N'@EmployeeDate'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '抽獎員工表主键', N'user', N'dbo', N'procedure', N'proc_Update_Employee', N'parameter', N'@EmployeeId'
GO

IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Update_EmployeeField')
DROP PROCEDURE dbo.proc_Update_EmployeeField
GO
CREATE PROCEDURE dbo.proc_Update_EmployeeField
(
 @EmployeeId Int,
 @FieldValue NVARCHAR(1500)
)
AS
DECLARE @sql NVARCHAR(4000)
SET @sql='update  dbo.Employee set ' @FieldValue ' WHERE EmployeeId ='  CAST(@EmployeeId AS VARCHAR(50))
EXEC(@sql)
GO

EXECUTE sp_addextendedproperty N'MS_Description', '抽獎員工表修改某一列数据的存储过程', N'user', N'dbo', N'procedure', N'proc_Update_EmployeeField', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '抽獎員工表修改字段名及其值如:[Remark]=A', N'user', N'dbo', N'procedure', N'proc_Update_EmployeeField', N'parameter', N'@FieldValue'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '抽獎員工表主键', N'user', N'dbo', N'procedure', N'proc_Update_EmployeeField', N'parameter', N'@EmployeeId'
GO

IF EXISTS (select * from sysobjects where [name] = 'dbo.proc_Delete_Employee')
DROP PROCEDURE dbo.proc_Delete_Employee
GO
CREATE PROCEDURE dbo.proc_Delete_Employee
(
 @EmployeeId Int
)
as
DELETE
 dbo.Employee
 WHERE
  EmployeeId = @EmployeeId
GO

EXECUTE sp_addextendedproperty N'MS_Description', '抽獎員工表删除记录的存储过程', N'user', N'dbo', N'procedure', N'proc_Delete_Employee', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '抽獎員工表主键', N'user', N'dbo', N'procedure', N'proc_Delete_Employee', N'parameter', N'@EmployeeId'
GO

IF EXISTS (select * from sysobjects where [name] = 'dbo.proc_Delete_EmployeeId')
DROP PROCEDURE dbo.proc_Delete_EmployeeId
GO
CREATE PROCEDURE dbo.proc_Delete_EmployeeId
(
 @EmployeeId varchar(2000)
)
as
DECLARE @strsql varchar(3000)
SET @strsql='DELETE dbo.Employee WHERE EmployeeId in(' @EmployeeId ')'
EXEC(@strsql)
GO

EXECUTE sp_addextendedproperty N'MS_Description', '抽獎員工表删除多条记录的存储过程', N'user', N'dbo', N'procedure', N'proc_Delete_EmployeeId', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '抽獎員工表主键', N'user', N'dbo', N'procedure', N'proc_Delete_EmployeeId', N'parameter', N'@EmployeeId'
GO

IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Select_Employee')
DROP PROCEDURE dbo.proc_Select_Employee
GO
CREATE PROCEDURE dbo.proc_Select_Employee
(
 @EmployeeId Int
)
AS
SELECT * FROM dbo.Employee WHERE EmployeeId = @EmployeeId
GO

EXECUTE sp_addextendedproperty N'MS_Description', '抽獎員工表查询记录的存储过程', N'user', N'dbo', N'procedure', N'proc_Select_Employee', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '抽獎員工表主键', N'user', N'dbo', N'procedure', N'proc_Select_Employee', N'parameter', N'@EmployeeId'
GO

IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Select_EmployeeExists')
DROP PROCEDURE dbo.proc_Select_EmployeeExists
GO
CREATE PROCEDURE dbo.proc_Select_EmployeeExists
(
 @EmployeeId Int
)
AS
SELECT count(1) as H FROM dbo.Employee WHERE EmployeeId = @EmployeeId
GO

EXECUTE sp_addextendedproperty N'MS_Description', '抽獎員工表是否存在某记录的存储过程', N'user', N'dbo', N'procedure', N'proc_Select_EmployeeExists', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '抽獎員工表主键', N'user', N'dbo', N'procedure', N'proc_Select_EmployeeExists', N'parameter', N'@EmployeeId'
GO

IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Select_EmployeeCount')
DROP PROCEDURE dbo.proc_Select_EmployeeCount
GO
CREATE PROCEDURE dbo.proc_Select_EmployeeCount
(
 @where NVARCHAR(1000)
)
AS
DECLARE @sql NVARCHAR(4000)
SET @sql='select count(*) as H from dbo.Employee '
IF @where<>''
 SET @sql=@sql @where
EXEC(@sql)
GO

EXECUTE sp_addextendedproperty N'MS_Description', '抽獎員工表共有多少条记录的存储过程', N'user', N'dbo', N'procedure', N'proc_Select_EmployeeCount', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '抽獎員工表要模糊查询的字段条件', N'user', N'dbo', N'procedure', N'proc_Select_EmployeeCount', N'parameter', N'@where'
GO

IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Select_EmployeeCountView')
DROP PROCEDURE dbo.proc_Select_EmployeeCountView
GO
CREATE PROCEDURE dbo.proc_Select_EmployeeCountView
(
 @where NVARCHAR(1000)
)
AS
DECLARE @sql NVARCHAR(4000)
SET @sql='select count(*) as H from dbo.View_Employee '
IF @where<>''
 SET @sql=@sql @where
EXEC(@sql)
GO

EXECUTE sp_addextendedproperty N'MS_Description', '抽獎員工表视图查询共有多少条记录的存储过程', N'user', N'dbo', N'procedure', N'proc_Select_EmployeeCountView', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '抽獎員工表要模糊查询的字段条件', N'user', N'dbo', N'procedure', N'proc_Select_EmployeeCountView', N'parameter', N'@where'
GO

IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Select_EmployeeTitle')
DROP PROCEDURE dbo.proc_Select_EmployeeTitle
GO
CREATE PROCEDURE dbo.proc_Select_EmployeeTitle
(
 @FieldName NVARCHAR(100),
 @EmployeeId Int
)
AS
DECLARE @sql NVARCHAR(4000)
SET @sql='select top 1 ' @FieldName ' from dbo.Employee WHERE EmployeeId ='  CAST(@EmployeeId AS VARCHAR(50))
EXEC(@sql)
GO

EXECUTE sp_addextendedproperty N'MS_Description', '抽獎員工表返回某字段的标题记录的存储过程', N'user', N'dbo', N'procedure', N'proc_Select_EmployeeTitle', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '抽獎員工表要显示的字段名的列表', N'user', N'dbo', N'procedure', N'proc_Select_EmployeeTitle', N'parameter', N'@FieldName'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '抽獎員工表主键', N'user', N'dbo', N'procedure', N'proc_Select_EmployeeTitle', N'parameter', N'@EmployeeId'
GO

IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Select_EmployeeFuzzySearch')
DROP PROCEDURE dbo.proc_Select_EmployeeFuzzySearch
GO
CREATE PROCEDURE dbo.proc_Select_EmployeeFuzzySearch
(
 @FieldList NVARCHAR(1000),
 @where NVARCHAR(2000)
)
AS
DECLARE @sql NVARCHAR(4000)
IF(@where<>'')
 SET @where=' WHERE ' @where 
IF(@FieldList='')
 SET @FieldList=' * ' 
SET @sql='select ' @FieldList ' from dbo.Employee ' @where
EXEC(@sql)
GO

EXECUTE sp_addextendedproperty N'MS_Description', '抽獎員工表模糊查询记录的存储过程', N'user', N'dbo', N'procedure', N'proc_Select_Employee', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '抽獎員工表要显示的字段名的列表', N'user', N'dbo', N'procedure', N'proc_Select_Employee', N'parameter', N'@FieldList'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '抽獎員工表要模糊查询的字段条件', N'user', N'dbo', N'procedure', N'proc_Select_Employee', N'parameter', N'@where'
GO

IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Select_EmployeeAll')
DROP PROCEDURE dbo.proc_Select_EmployeeAll
GO
CREATE PROCEDURE dbo.proc_Select_EmployeeAll
AS
SELECT * FROM dbo.Employee
GO

EXECUTE sp_addextendedproperty N'MS_Description', '抽獎員工表查询所有记录存储过程', N'user', N'dbo', N'procedure', N'proc_Select_EmployeeAll', NULL, NULL
GO


IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Insert_AwardType')
DROP PROCEDURE dbo.proc_Insert_AwardType
GO
CREATE PROCEDURE dbo.proc_Insert_AwardType
(
 @AwardProjectId Int,
 @AwardName NVarChar(200),
 @AwardPersonNumber Int,
 @AwardVeryNumber Int,
 @AwardVeryProduct Int
)
AS
INSERT INTO dbo.AwardType
(
 [AwardProjectId] ,
 [AwardName] ,
 [AwardPersonNumber] ,
 [AwardVeryNumber] ,
 [AwardVeryProduct]
)
 VALUES
(
 @AwardProjectId ,
 @AwardName ,
 @AwardPersonNumber ,
 @AwardVeryNumber ,
 @AwardVeryProduct
)
GO

EXECUTE sp_addextendedproperty N'MS_Description', '奖项表 增加记录存储过程', N'user', N'dbo', N'procedure', N'proc_Insert_AwardType', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '外鍵,項目ID', N'user', N'dbo', N'procedure', N'proc_Insert_AwardType', N'parameter', N'@AwardProjectId'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '獎項名稱,如:特等獎,一等獎', N'user', N'dbo', N'procedure', N'proc_Insert_AwardType', N'parameter', N'@AwardName'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '獎項人數', N'user', N'dbo', N'procedure', N'proc_Insert_AwardType', N'parameter', N'@AwardPersonNumber'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '每次抽的人數', N'user', N'dbo', N'procedure', N'proc_Insert_AwardType', N'parameter', N'@AwardVeryNumber'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '獎項每人產品數量', N'user', N'dbo', N'procedure', N'proc_Insert_AwardType', N'parameter', N'@AwardVeryProduct'
GO


IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Insert_AwardTypeOutput')
DROP PROCEDURE dbo.proc_Insert_AwardTypeOutput
GO
CREATE PROCEDURE dbo.proc_Insert_AwardTypeOutput
(
 @AwardProjectId Int,
 @AwardName NVarChar(200),
 @AwardPersonNumber Int,
 @AwardVeryNumber Int,
 @AwardVeryProduct Int,
 @AwardTypeId int  output
)
AS
INSERT INTO dbo.AwardType
(
 [AwardProjectId] ,
 [AwardName] ,
 [AwardPersonNumber] ,
 [AwardVeryNumber] ,
 [AwardVeryProduct]
)
 VALUES
(
 @AwardProjectId ,
 @AwardName ,
 @AwardPersonNumber ,
 @AwardVeryNumber ,
 @AwardVeryProduct
)
select @AwardTypeId=@@IDENTITY
GO

EXECUTE sp_addextendedproperty N'MS_Description', '奖项表 添加记录有返回值的存储过程', N'user', N'dbo', N'procedure', N'proc_Insert_AwardType', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '外鍵,項目ID', N'user', N'dbo', N'procedure', N'proc_Insert_AwardType', N'parameter', N'@AwardProjectId'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '獎項名稱,如:特等獎,一等獎', N'user', N'dbo', N'procedure', N'proc_Insert_AwardType', N'parameter', N'@AwardName'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '獎項人數', N'user', N'dbo', N'procedure', N'proc_Insert_AwardType', N'parameter', N'@AwardPersonNumber'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '每次抽的人數', N'user', N'dbo', N'procedure', N'proc_Insert_AwardType', N'parameter', N'@AwardVeryNumber'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '獎項每人產品數量', N'user', N'dbo', N'procedure', N'proc_Insert_AwardType', N'parameter', N'@AwardVeryProduct'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '奖项表 主键输出', N'user', N'dbo', N'procedure', N'proc_Insert_AwardType', N'parameter', N'@AwardTypeId'
GO

IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Update_AwardType')
DROP PROCEDURE dbo.proc_Update_AwardType
GO
CREATE PROCEDURE dbo.proc_Update_AwardType
(
 @AwardTypeId Int,
 @AwardProjectId Int,
 @AwardName NVarChar(200),
 @AwardPersonNumber Int,
 @AwardVeryNumber Int,
 @AwardVeryProduct Int
)
AS
UPDATE dbo.AwardType
 SET
  [AwardProjectId]=@AwardProjectId ,
  [AwardName]=@AwardName ,
  [AwardPersonNumber]=@AwardPersonNumber ,
  [AwardVeryNumber]=@AwardVeryNumber ,
  [AwardVeryProduct]=@AwardVeryProduct
 where
  [AwardTypeId]=@AwardTypeId
GO

EXECUTE sp_addextendedproperty N'MS_Description', '奖项表 修改记录的存储过程', N'user', N'dbo', N'procedure', N'proc_Update_AwardType', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '外鍵,項目ID', N'user', N'dbo', N'procedure', N'proc_Update_AwardType', N'parameter', N'@AwardProjectId'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '獎項名稱,如:特等獎,一等獎', N'user', N'dbo', N'procedure', N'proc_Update_AwardType', N'parameter', N'@AwardName'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '獎項人數', N'user', N'dbo', N'procedure', N'proc_Update_AwardType', N'parameter', N'@AwardPersonNumber'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '每次抽的人數', N'user', N'dbo', N'procedure', N'proc_Update_AwardType', N'parameter', N'@AwardVeryNumber'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '獎項每人產品數量', N'user', N'dbo', N'procedure', N'proc_Update_AwardType', N'parameter', N'@AwardVeryProduct'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '奖项表 主键', N'user', N'dbo', N'procedure', N'proc_Update_AwardType', N'parameter', N'@AwardTypeId'
GO

IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Update_AwardTypeField')
DROP PROCEDURE dbo.proc_Update_AwardTypeField
GO
CREATE PROCEDURE dbo.proc_Update_AwardTypeField
(
 @AwardTypeId Int,
 @FieldValue NVARCHAR(1500)
)
AS
DECLARE @sql NVARCHAR(4000)
SET @sql='update  dbo.AwardType set ' @FieldValue ' WHERE AwardTypeId ='  CAST(@AwardTypeId AS VARCHAR(50))
EXEC(@sql)
GO

EXECUTE sp_addextendedproperty N'MS_Description', '奖项表 修改某一列数据的存储过程', N'user', N'dbo', N'procedure', N'proc_Update_AwardTypeField', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '奖项表 修改字段名及其值如:[Remark]=A', N'user', N'dbo', N'procedure', N'proc_Update_AwardTypeField', N'parameter', N'@FieldValue'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '奖项表 主键', N'user', N'dbo', N'procedure', N'proc_Update_AwardTypeField', N'parameter', N'@AwardTypeId'
GO

IF EXISTS (select * from sysobjects where [name] = 'dbo.proc_Delete_AwardType')
DROP PROCEDURE dbo.proc_Delete_AwardType
GO
CREATE PROCEDURE dbo.proc_Delete_AwardType
(
 @AwardTypeId Int
)
as
DELETE
 dbo.AwardType
 WHERE
  AwardTypeId = @AwardTypeId
GO

EXECUTE sp_addextendedproperty N'MS_Description', '奖项表 删除记录的存储过程', N'user', N'dbo', N'procedure', N'proc_Delete_AwardType', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '奖项表 主键', N'user', N'dbo', N'procedure', N'proc_Delete_AwardType', N'parameter', N'@AwardTypeId'
GO

IF EXISTS (select * from sysobjects where [name] = 'dbo.proc_Delete_AwardTypeId')
DROP PROCEDURE dbo.proc_Delete_AwardTypeId
GO
CREATE PROCEDURE dbo.proc_Delete_AwardTypeId
(
 @AwardTypeId varchar(2000)
)
as
DECLARE @strsql varchar(3000)
SET @strsql='DELETE dbo.AwardType WHERE AwardTypeId in(' @AwardTypeId ')'
EXEC(@strsql)
GO

EXECUTE sp_addextendedproperty N'MS_Description', '奖项表 删除多条记录的存储过程', N'user', N'dbo', N'procedure', N'proc_Delete_AwardTypeId', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '奖项表 主键', N'user', N'dbo', N'procedure', N'proc_Delete_AwardTypeId', N'parameter', N'@AwardTypeId'
GO

IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Select_AwardType')
DROP PROCEDURE dbo.proc_Select_AwardType
GO
CREATE PROCEDURE dbo.proc_Select_AwardType
(
 @AwardTypeId Int
)
AS
SELECT * FROM dbo.AwardType WHERE AwardTypeId = @AwardTypeId
GO

EXECUTE sp_addextendedproperty N'MS_Description', '奖项表 查询记录的存储过程', N'user', N'dbo', N'procedure', N'proc_Select_AwardType', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '奖项表 主键', N'user', N'dbo', N'procedure', N'proc_Select_AwardType', N'parameter', N'@AwardTypeId'
GO

IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Select_AwardTypeExists')
DROP PROCEDURE dbo.proc_Select_AwardTypeExists
GO
CREATE PROCEDURE dbo.proc_Select_AwardTypeExists
(
 @AwardTypeId Int
)
AS
SELECT count(1) as H FROM dbo.AwardType WHERE AwardTypeId = @AwardTypeId
GO

EXECUTE sp_addextendedproperty N'MS_Description', '奖项表 是否存在某记录的存储过程', N'user', N'dbo', N'procedure', N'proc_Select_AwardTypeExists', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '奖项表 主键', N'user', N'dbo', N'procedure', N'proc_Select_AwardTypeExists', N'parameter', N'@AwardTypeId'
GO

IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Select_AwardTypeCount')
DROP PROCEDURE dbo.proc_Select_AwardTypeCount
GO
CREATE PROCEDURE dbo.proc_Select_AwardTypeCount
(
 @where NVARCHAR(1000)
)
AS
DECLARE @sql NVARCHAR(4000)
SET @sql='select count(*) as H from dbo.AwardType '
IF @where<>''
 SET @sql=@sql @where
EXEC(@sql)
GO

EXECUTE sp_addextendedproperty N'MS_Description', '奖项表 共有多少条记录的存储过程', N'user', N'dbo', N'procedure', N'proc_Select_AwardTypeCount', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '奖项表 要模糊查询的字段条件', N'user', N'dbo', N'procedure', N'proc_Select_AwardTypeCount', N'parameter', N'@where'
GO

IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Select_AwardTypeCountView')
DROP PROCEDURE dbo.proc_Select_AwardTypeCountView
GO
CREATE PROCEDURE dbo.proc_Select_AwardTypeCountView
(
 @where NVARCHAR(1000)
)
AS
DECLARE @sql NVARCHAR(4000)
SET @sql='select count(*) as H from dbo.View_AwardType '
IF @where<>''
 SET @sql=@sql @where
EXEC(@sql)
GO

EXECUTE sp_addextendedproperty N'MS_Description', '奖项表 视图查询共有多少条记录的存储过程', N'user', N'dbo', N'procedure', N'proc_Select_AwardTypeCountView', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '奖项表 要模糊查询的字段条件', N'user', N'dbo', N'procedure', N'proc_Select_AwardTypeCountView', N'parameter', N'@where'
GO

IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Select_AwardTypeTitle')
DROP PROCEDURE dbo.proc_Select_AwardTypeTitle
GO
CREATE PROCEDURE dbo.proc_Select_AwardTypeTitle
(
 @FieldName NVARCHAR(100),
 @AwardTypeId Int
)
AS
DECLARE @sql NVARCHAR(4000)
SET @sql='select top 1 ' @FieldName ' from dbo.AwardType WHERE AwardTypeId ='  CAST(@AwardTypeId AS VARCHAR(50))
EXEC(@sql)
GO

EXECUTE sp_addextendedproperty N'MS_Description', '奖项表 返回某字段的标题记录的存储过程', N'user', N'dbo', N'procedure', N'proc_Select_AwardTypeTitle', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '奖项表 要显示的字段名的列表', N'user', N'dbo', N'procedure', N'proc_Select_AwardTypeTitle', N'parameter', N'@FieldName'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '奖项表 主键', N'user', N'dbo', N'procedure', N'proc_Select_AwardTypeTitle', N'parameter', N'@AwardTypeId'
GO

IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Select_AwardTypeFuzzySearch')
DROP PROCEDURE dbo.proc_Select_AwardTypeFuzzySearch
GO
CREATE PROCEDURE dbo.proc_Select_AwardTypeFuzzySearch
(
 @FieldList NVARCHAR(1000),
 @where NVARCHAR(2000)
)
AS
DECLARE @sql NVARCHAR(4000)
IF(@where<>'')
 SET @where=' WHERE ' @where 
IF(@FieldList='')
 SET @FieldList=' * ' 
SET @sql='select ' @FieldList ' from dbo.AwardType ' @where
EXEC(@sql)
GO

EXECUTE sp_addextendedproperty N'MS_Description', '奖项表 模糊查询记录的存储过程', N'user', N'dbo', N'procedure', N'proc_Select_AwardType', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '奖项表 要显示的字段名的列表', N'user', N'dbo', N'procedure', N'proc_Select_AwardType', N'parameter', N'@FieldList'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '奖项表 要模糊查询的字段条件', N'user', N'dbo', N'procedure', N'proc_Select_AwardType', N'parameter', N'@where'
GO

IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Select_AwardTypeAll')
DROP PROCEDURE dbo.proc_Select_AwardTypeAll
GO
CREATE PROCEDURE dbo.proc_Select_AwardTypeAll
AS
SELECT * FROM dbo.AwardType
GO

EXECUTE sp_addextendedproperty N'MS_Description', '奖项表 查询所有记录存储过程', N'user', N'dbo', N'procedure', N'proc_Select_AwardTypeAll', NULL, NULL
GO


IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Insert_AwardProduct')
DROP PROCEDURE dbo.proc_Insert_AwardProduct
GO
CREATE PROCEDURE dbo.proc_Insert_AwardProduct
(
 @ProductAwardTypeId Int,
 @ProductName NVarChar(400),
 @ProductNo VarChar(50),
 @ProductTotal Int
)
AS
INSERT INTO dbo.AwardProduct
(
 [ProductAwardTypeId] ,
 [ProductName] ,
 [ProductNo] ,
 [ProductTotal]
)
 VALUES
(
 @ProductAwardTypeId ,
 @ProductName ,
 @ProductNo ,
 @ProductTotal
)
GO

EXECUTE sp_addextendedproperty N'MS_Description', '奖品表增加记录存储过程', N'user', N'dbo', N'procedure', N'proc_Insert_AwardProduct', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '外鍵,獎項類型ID', N'user', N'dbo', N'procedure', N'proc_Insert_AwardProduct', N'parameter', N'@ProductAwardTypeId'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '獎品名稱', N'user', N'dbo', N'procedure', N'proc_Insert_AwardProduct', N'parameter', N'@ProductName'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '獎品編號', N'user', N'dbo', N'procedure', N'proc_Insert_AwardProduct', N'parameter', N'@ProductNo'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '獎品數量', N'user', N'dbo', N'procedure', N'proc_Insert_AwardProduct', N'parameter', N'@ProductTotal'
GO


IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Insert_AwardProductOutput')
DROP PROCEDURE dbo.proc_Insert_AwardProductOutput
GO
CREATE PROCEDURE dbo.proc_Insert_AwardProductOutput
(
 @ProductAwardTypeId Int,
 @ProductName NVarChar(400),
 @ProductNo VarChar(50),
 @ProductTotal Int,
 @ProductId int  output
)
AS
INSERT INTO dbo.AwardProduct
(
 [ProductAwardTypeId] ,
 [ProductName] ,
 [ProductNo] ,
 [ProductTotal]
)
 VALUES
(
 @ProductAwardTypeId ,
 @ProductName ,
 @ProductNo ,
 @ProductTotal
)
select @ProductId=@@IDENTITY
GO

EXECUTE sp_addextendedproperty N'MS_Description', '奖品表添加记录有返回值的存储过程', N'user', N'dbo', N'procedure', N'proc_Insert_AwardProduct', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '外鍵,獎項類型ID', N'user', N'dbo', N'procedure', N'proc_Insert_AwardProduct', N'parameter', N'@ProductAwardTypeId'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '獎品名稱', N'user', N'dbo', N'procedure', N'proc_Insert_AwardProduct', N'parameter', N'@ProductName'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '獎品編號', N'user', N'dbo', N'procedure', N'proc_Insert_AwardProduct', N'parameter', N'@ProductNo'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '獎品數量', N'user', N'dbo', N'procedure', N'proc_Insert_AwardProduct', N'parameter', N'@ProductTotal'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '奖品表主键输出', N'user', N'dbo', N'procedure', N'proc_Insert_AwardProduct', N'parameter', N'@ProductId'
GO

IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Update_AwardProduct')
DROP PROCEDURE dbo.proc_Update_AwardProduct
GO
CREATE PROCEDURE dbo.proc_Update_AwardProduct
(
 @ProductId Int,
 @ProductAwardTypeId Int,
 @ProductName NVarChar(400),
 @ProductNo VarChar(50),
 @ProductTotal Int
)
AS
UPDATE dbo.AwardProduct
 SET
  [ProductAwardTypeId]=@ProductAwardTypeId ,
  [ProductName]=@ProductName ,
  [ProductNo]=@ProductNo ,
  [ProductTotal]=@ProductTotal
 where
  [ProductId]=@ProductId
GO

EXECUTE sp_addextendedproperty N'MS_Description', '奖品表修改记录的存储过程', N'user', N'dbo', N'procedure', N'proc_Update_AwardProduct', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '外鍵,獎項類型ID', N'user', N'dbo', N'procedure', N'proc_Update_AwardProduct', N'parameter', N'@ProductAwardTypeId'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '獎品名稱', N'user', N'dbo', N'procedure', N'proc_Update_AwardProduct', N'parameter', N'@ProductName'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '獎品編號', N'user', N'dbo', N'procedure', N'proc_Update_AwardProduct', N'parameter', N'@ProductNo'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '獎品數量', N'user', N'dbo', N'procedure', N'proc_Update_AwardProduct', N'parameter', N'@ProductTotal'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '奖品表主键', N'user', N'dbo', N'procedure', N'proc_Update_AwardProduct', N'parameter', N'@ProductId'
GO

IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Update_AwardProductField')
DROP PROCEDURE dbo.proc_Update_AwardProductField
GO
CREATE PROCEDURE dbo.proc_Update_AwardProductField
(
 @ProductId Int,
 @FieldValue NVARCHAR(1500)
)
AS
DECLARE @sql NVARCHAR(4000)
SET @sql='update  dbo.AwardProduct set ' @FieldValue ' WHERE ProductId ='  CAST(@ProductId AS VARCHAR(50))
EXEC(@sql)
GO

EXECUTE sp_addextendedproperty N'MS_Description', '奖品表修改某一列数据的存储过程', N'user', N'dbo', N'procedure', N'proc_Update_AwardProductField', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '奖品表修改字段名及其值如:[Remark]=A', N'user', N'dbo', N'procedure', N'proc_Update_AwardProductField', N'parameter', N'@FieldValue'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '奖品表主键', N'user', N'dbo', N'procedure', N'proc_Update_AwardProductField', N'parameter', N'@ProductId'
GO

IF EXISTS (select * from sysobjects where [name] = 'dbo.proc_Delete_AwardProduct')
DROP PROCEDURE dbo.proc_Delete_AwardProduct
GO
CREATE PROCEDURE dbo.proc_Delete_AwardProduct
(
 @ProductId Int
)
as
DELETE
 dbo.AwardProduct
 WHERE
  ProductId = @ProductId
GO

EXECUTE sp_addextendedproperty N'MS_Description', '奖品表删除记录的存储过程', N'user', N'dbo', N'procedure', N'proc_Delete_AwardProduct', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '奖品表主键', N'user', N'dbo', N'procedure', N'proc_Delete_AwardProduct', N'parameter', N'@ProductId'
GO

IF EXISTS (select * from sysobjects where [name] = 'dbo.proc_Delete_AwardProductId')
DROP PROCEDURE dbo.proc_Delete_AwardProductId
GO
CREATE PROCEDURE dbo.proc_Delete_AwardProductId
(
 @ProductId varchar(2000)
)
as
DECLARE @strsql varchar(3000)
SET @strsql='DELETE dbo.AwardProduct WHERE ProductId in(' @ProductId ')'
EXEC(@strsql)
GO

EXECUTE sp_addextendedproperty N'MS_Description', '奖品表删除多条记录的存储过程', N'user', N'dbo', N'procedure', N'proc_Delete_AwardProductId', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '奖品表主键', N'user', N'dbo', N'procedure', N'proc_Delete_AwardProductId', N'parameter', N'@ProductId'
GO

IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Select_AwardProduct')
DROP PROCEDURE dbo.proc_Select_AwardProduct
GO
CREATE PROCEDURE dbo.proc_Select_AwardProduct
(
 @ProductId Int
)
AS
SELECT * FROM dbo.AwardProduct WHERE ProductId = @ProductId
GO

EXECUTE sp_addextendedproperty N'MS_Description', '奖品表查询记录的存储过程', N'user', N'dbo', N'procedure', N'proc_Select_AwardProduct', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '奖品表主键', N'user', N'dbo', N'procedure', N'proc_Select_AwardProduct', N'parameter', N'@ProductId'
GO

IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Select_AwardProductExists')
DROP PROCEDURE dbo.proc_Select_AwardProductExists
GO
CREATE PROCEDURE dbo.proc_Select_AwardProductExists
(
 @ProductId Int
)
AS
SELECT count(1) as H FROM dbo.AwardProduct WHERE ProductId = @ProductId
GO

EXECUTE sp_addextendedproperty N'MS_Description', '奖品表是否存在某记录的存储过程', N'user', N'dbo', N'procedure', N'proc_Select_AwardProductExists', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '奖品表主键', N'user', N'dbo', N'procedure', N'proc_Select_AwardProductExists', N'parameter', N'@ProductId'
GO

IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Select_AwardProductCount')
DROP PROCEDURE dbo.proc_Select_AwardProductCount
GO
CREATE PROCEDURE dbo.proc_Select_AwardProductCount
(
 @where NVARCHAR(1000)
)
AS
DECLARE @sql NVARCHAR(4000)
SET @sql='select count(*) as H from dbo.AwardProduct '
IF @where<>''
 SET @sql=@sql @where
EXEC(@sql)
GO

EXECUTE sp_addextendedproperty N'MS_Description', '奖品表共有多少条记录的存储过程', N'user', N'dbo', N'procedure', N'proc_Select_AwardProductCount', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '奖品表要模糊查询的字段条件', N'user', N'dbo', N'procedure', N'proc_Select_AwardProductCount', N'parameter', N'@where'
GO

IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Select_AwardProductCountView')
DROP PROCEDURE dbo.proc_Select_AwardProductCountView
GO
CREATE PROCEDURE dbo.proc_Select_AwardProductCountView
(
 @where NVARCHAR(1000)
)
AS
DECLARE @sql NVARCHAR(4000)
SET @sql='select count(*) as H from dbo.View_AwardProduct '
IF @where<>''
 SET @sql=@sql @where
EXEC(@sql)
GO

EXECUTE sp_addextendedproperty N'MS_Description', '奖品表视图查询共有多少条记录的存储过程', N'user', N'dbo', N'procedure', N'proc_Select_AwardProductCountView', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '奖品表要模糊查询的字段条件', N'user', N'dbo', N'procedure', N'proc_Select_AwardProductCountView', N'parameter', N'@where'
GO

IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Select_AwardProductTitle')
DROP PROCEDURE dbo.proc_Select_AwardProductTitle
GO
CREATE PROCEDURE dbo.proc_Select_AwardProductTitle
(
 @FieldName NVARCHAR(100),
 @ProductId Int
)
AS
DECLARE @sql NVARCHAR(4000)
SET @sql='select top 1 ' @FieldName ' from dbo.AwardProduct WHERE ProductId ='  CAST(@ProductId AS VARCHAR(50))
EXEC(@sql)
GO

EXECUTE sp_addextendedproperty N'MS_Description', '奖品表返回某字段的标题记录的存储过程', N'user', N'dbo', N'procedure', N'proc_Select_AwardProductTitle', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '奖品表要显示的字段名的列表', N'user', N'dbo', N'procedure', N'proc_Select_AwardProductTitle', N'parameter', N'@FieldName'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '奖品表主键', N'user', N'dbo', N'procedure', N'proc_Select_AwardProductTitle', N'parameter', N'@ProductId'
GO

IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Select_AwardProductFuzzySearch')
DROP PROCEDURE dbo.proc_Select_AwardProductFuzzySearch
GO
CREATE PROCEDURE dbo.proc_Select_AwardProductFuzzySearch
(
 @FieldList NVARCHAR(1000),
 @where NVARCHAR(2000)
)
AS
DECLARE @sql NVARCHAR(4000)
IF(@where<>'')
 SET @where=' WHERE ' @where 
IF(@FieldList='')
 SET @FieldList=' * ' 
SET @sql='select ' @FieldList ' from dbo.AwardProduct ' @where
EXEC(@sql)
GO

EXECUTE sp_addextendedproperty N'MS_Description', '奖品表模糊查询记录的存储过程', N'user', N'dbo', N'procedure', N'proc_Select_AwardProduct', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '奖品表要显示的字段名的列表', N'user', N'dbo', N'procedure', N'proc_Select_AwardProduct', N'parameter', N'@FieldList'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '奖品表要模糊查询的字段条件', N'user', N'dbo', N'procedure', N'proc_Select_AwardProduct', N'parameter', N'@where'
GO

IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Select_AwardProductAll')
DROP PROCEDURE dbo.proc_Select_AwardProductAll
GO
CREATE PROCEDURE dbo.proc_Select_AwardProductAll
AS
SELECT * FROM dbo.AwardProduct
GO

EXECUTE sp_addextendedproperty N'MS_Description', '奖品表查询所有记录存储过程', N'user', N'dbo', N'procedure', N'proc_Select_AwardProductAll', NULL, NULL
GO


IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Insert_AwardProductImage')
DROP PROCEDURE dbo.proc_Insert_AwardProductImage
GO
CREATE PROCEDURE dbo.proc_Insert_AwardProductImage
(
 @ProductImageProductId Int,
 @ProductImageByte Image
)
AS
INSERT INTO dbo.AwardProductImage
(
 [ProductImageProductId] ,
 [ProductImageByte]
)
 VALUES
(
 @ProductImageProductId ,
 @ProductImageByte
)
GO

EXECUTE sp_addextendedproperty N'MS_Description', '獎品圖片表增加记录存储过程', N'user', N'dbo', N'procedure', N'proc_Insert_AwardProductImage', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '外鍵,產品ID', N'user', N'dbo', N'procedure', N'proc_Insert_AwardProductImage', N'parameter', N'@ProductImageProductId'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '產品圖片', N'user', N'dbo', N'procedure', N'proc_Insert_AwardProductImage', N'parameter', N'@ProductImageByte'
GO


IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Insert_AwardProductImageOutput')
DROP PROCEDURE dbo.proc_Insert_AwardProductImageOutput
GO
CREATE PROCEDURE dbo.proc_Insert_AwardProductImageOutput
(
 @ProductImageProductId Int,
 @ProductImageByte Image,
 @ProductImageId int  output
)
AS
INSERT INTO dbo.AwardProductImage
(
 [ProductImageProductId] ,
 [ProductImageByte]
)
 VALUES
(
 @ProductImageProductId ,
 @ProductImageByte
)
select @ProductImageId=@@IDENTITY
GO

EXECUTE sp_addextendedproperty N'MS_Description', '獎品圖片表添加记录有返回值的存储过程', N'user', N'dbo', N'procedure', N'proc_Insert_AwardProductImage', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '外鍵,產品ID', N'user', N'dbo', N'procedure', N'proc_Insert_AwardProductImage', N'parameter', N'@ProductImageProductId'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '產品圖片', N'user', N'dbo', N'procedure', N'proc_Insert_AwardProductImage', N'parameter', N'@ProductImageByte'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '獎品圖片表主键输出', N'user', N'dbo', N'procedure', N'proc_Insert_AwardProductImage', N'parameter', N'@ProductImageId'
GO

IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Update_AwardProductImage')
DROP PROCEDURE dbo.proc_Update_AwardProductImage
GO
CREATE PROCEDURE dbo.proc_Update_AwardProductImage
(
 @ProductImageId Int,
 @ProductImageProductId Int,
 @ProductImageByte Image
)
AS
UPDATE dbo.AwardProductImage
 SET
  [ProductImageProductId]=@ProductImageProductId ,
  [ProductImageByte]=@ProductImageByte
 where
  [ProductImageId]=@ProductImageId
GO

EXECUTE sp_addextendedproperty N'MS_Description', '獎品圖片表修改记录的存储过程', N'user', N'dbo', N'procedure', N'proc_Update_AwardProductImage', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '外鍵,產品ID', N'user', N'dbo', N'procedure', N'proc_Update_AwardProductImage', N'parameter', N'@ProductImageProductId'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '產品圖片', N'user', N'dbo', N'procedure', N'proc_Update_AwardProductImage', N'parameter', N'@ProductImageByte'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '獎品圖片表主键', N'user', N'dbo', N'procedure', N'proc_Update_AwardProductImage', N'parameter', N'@ProductImageId'
GO

IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Update_AwardProductImageField')
DROP PROCEDURE dbo.proc_Update_AwardProductImageField
GO
CREATE PROCEDURE dbo.proc_Update_AwardProductImageField
(
 @ProductImageId Int,
 @FieldValue NVARCHAR(1500)
)
AS
DECLARE @sql NVARCHAR(4000)
SET @sql='update  dbo.AwardProductImage set ' @FieldValue ' WHERE ProductImageId ='  CAST(@ProductImageId AS VARCHAR(50))
EXEC(@sql)
GO

EXECUTE sp_addextendedproperty N'MS_Description', '獎品圖片表修改某一列数据的存储过程', N'user', N'dbo', N'procedure', N'proc_Update_AwardProductImageField', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '獎品圖片表修改字段名及其值如:[Remark]=A', N'user', N'dbo', N'procedure', N'proc_Update_AwardProductImageField', N'parameter', N'@FieldValue'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '獎品圖片表主键', N'user', N'dbo', N'procedure', N'proc_Update_AwardProductImageField', N'parameter', N'@ProductImageId'
GO

IF EXISTS (select * from sysobjects where [name] = 'dbo.proc_Delete_AwardProductImage')
DROP PROCEDURE dbo.proc_Delete_AwardProductImage
GO
CREATE PROCEDURE dbo.proc_Delete_AwardProductImage
(
 @ProductImageId Int
)
as
DELETE
 dbo.AwardProductImage
 WHERE
  ProductImageId = @ProductImageId
GO

EXECUTE sp_addextendedproperty N'MS_Description', '獎品圖片表删除记录的存储过程', N'user', N'dbo', N'procedure', N'proc_Delete_AwardProductImage', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '獎品圖片表主键', N'user', N'dbo', N'procedure', N'proc_Delete_AwardProductImage', N'parameter', N'@ProductImageId'
GO

IF EXISTS (select * from sysobjects where [name] = 'dbo.proc_Delete_AwardProductImageId')
DROP PROCEDURE dbo.proc_Delete_AwardProductImageId
GO
CREATE PROCEDURE dbo.proc_Delete_AwardProductImageId
(
 @ProductImageId varchar(2000)
)
as
DECLARE @strsql varchar(3000)
SET @strsql='DELETE dbo.AwardProductImage WHERE ProductImageId in(' @ProductImageId ')'
EXEC(@strsql)
GO

EXECUTE sp_addextendedproperty N'MS_Description', '獎品圖片表删除多条记录的存储过程', N'user', N'dbo', N'procedure', N'proc_Delete_AwardProductImageId', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '獎品圖片表主键', N'user', N'dbo', N'procedure', N'proc_Delete_AwardProductImageId', N'parameter', N'@ProductImageId'
GO

IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Select_AwardProductImage')
DROP PROCEDURE dbo.proc_Select_AwardProductImage
GO
CREATE PROCEDURE dbo.proc_Select_AwardProductImage
(
 @ProductImageId Int
)
AS
SELECT * FROM dbo.AwardProductImage WHERE ProductImageId = @ProductImageId
GO

EXECUTE sp_addextendedproperty N'MS_Description', '獎品圖片表查询记录的存储过程', N'user', N'dbo', N'procedure', N'proc_Select_AwardProductImage', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '獎品圖片表主键', N'user', N'dbo', N'procedure', N'proc_Select_AwardProductImage', N'parameter', N'@ProductImageId'
GO

IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Select_AwardProductImageExists')
DROP PROCEDURE dbo.proc_Select_AwardProductImageExists
GO
CREATE PROCEDURE dbo.proc_Select_AwardProductImageExists
(
 @ProductImageId Int
)
AS
SELECT count(1) as H FROM dbo.AwardProductImage WHERE ProductImageId = @ProductImageId
GO

EXECUTE sp_addextendedproperty N'MS_Description', '獎品圖片表是否存在某记录的存储过程', N'user', N'dbo', N'procedure', N'proc_Select_AwardProductImageExists', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '獎品圖片表主键', N'user', N'dbo', N'procedure', N'proc_Select_AwardProductImageExists', N'parameter', N'@ProductImageId'
GO

IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Select_AwardProductImageCount')
DROP PROCEDURE dbo.proc_Select_AwardProductImageCount
GO
CREATE PROCEDURE dbo.proc_Select_AwardProductImageCount
(
 @where NVARCHAR(1000)
)
AS
DECLARE @sql NVARCHAR(4000)
SET @sql='select count(*) as H from dbo.AwardProductImage '
IF @where<>''
 SET @sql=@sql @where
EXEC(@sql)
GO

EXECUTE sp_addextendedproperty N'MS_Description', '獎品圖片表共有多少条记录的存储过程', N'user', N'dbo', N'procedure', N'proc_Select_AwardProductImageCount', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '獎品圖片表要模糊查询的字段条件', N'user', N'dbo', N'procedure', N'proc_Select_AwardProductImageCount', N'parameter', N'@where'
GO

IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Select_AwardProductImageCountView')
DROP PROCEDURE dbo.proc_Select_AwardProductImageCountView
GO
CREATE PROCEDURE dbo.proc_Select_AwardProductImageCountView
(
 @where NVARCHAR(1000)
)
AS
DECLARE @sql NVARCHAR(4000)
SET @sql='select count(*) as H from dbo.View_AwardProductImage '
IF @where<>''
 SET @sql=@sql @where
EXEC(@sql)
GO

EXECUTE sp_addextendedproperty N'MS_Description', '獎品圖片表视图查询共有多少条记录的存储过程', N'user', N'dbo', N'procedure', N'proc_Select_AwardProductImageCountView', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '獎品圖片表要模糊查询的字段条件', N'user', N'dbo', N'procedure', N'proc_Select_AwardProductImageCountView', N'parameter', N'@where'
GO

IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Select_AwardProductImageTitle')
DROP PROCEDURE dbo.proc_Select_AwardProductImageTitle
GO
CREATE PROCEDURE dbo.proc_Select_AwardProductImageTitle
(
 @FieldName NVARCHAR(100),
 @ProductImageId Int
)
AS
DECLARE @sql NVARCHAR(4000)
SET @sql='select top 1 ' @FieldName ' from dbo.AwardProductImage WHERE ProductImageId ='  CAST(@ProductImageId AS VARCHAR(50))
EXEC(@sql)
GO

EXECUTE sp_addextendedproperty N'MS_Description', '獎品圖片表返回某字段的标题记录的存储过程', N'user', N'dbo', N'procedure', N'proc_Select_AwardProductImageTitle', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '獎品圖片表要显示的字段名的列表', N'user', N'dbo', N'procedure', N'proc_Select_AwardProductImageTitle', N'parameter', N'@FieldName'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '獎品圖片表主键', N'user', N'dbo', N'procedure', N'proc_Select_AwardProductImageTitle', N'parameter', N'@ProductImageId'
GO

IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Select_AwardProductImageFuzzySearch')
DROP PROCEDURE dbo.proc_Select_AwardProductImageFuzzySearch
GO
CREATE PROCEDURE dbo.proc_Select_AwardProductImageFuzzySearch
(
 @FieldList NVARCHAR(1000),
 @where NVARCHAR(2000)
)
AS
DECLARE @sql NVARCHAR(4000)
IF(@where<>'')
 SET @where=' WHERE ' @where 
IF(@FieldList='')
 SET @FieldList=' * ' 
SET @sql='select ' @FieldList ' from dbo.AwardProductImage ' @where
EXEC(@sql)
GO

EXECUTE sp_addextendedproperty N'MS_Description', '獎品圖片表模糊查询记录的存储过程', N'user', N'dbo', N'procedure', N'proc_Select_AwardProductImage', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '獎品圖片表要显示的字段名的列表', N'user', N'dbo', N'procedure', N'proc_Select_AwardProductImage', N'parameter', N'@FieldList'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '獎品圖片表要模糊查询的字段条件', N'user', N'dbo', N'procedure', N'proc_Select_AwardProductImage', N'parameter', N'@where'
GO

IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Select_AwardProductImageAll')
DROP PROCEDURE dbo.proc_Select_AwardProductImageAll
GO
CREATE PROCEDURE dbo.proc_Select_AwardProductImageAll
AS
SELECT * FROM dbo.AwardProductImage
GO

EXECUTE sp_addextendedproperty N'MS_Description', '獎品圖片表查询所有记录存储过程', N'user', N'dbo', N'procedure', N'proc_Select_AwardProductImageAll', NULL, NULL
GO


IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Insert_LuckyWinner')
DROP PROCEDURE dbo.proc_Insert_LuckyWinner
GO
CREATE PROCEDURE dbo.proc_Insert_LuckyWinner
(
 @WinnerEmployeeId Int,
 @WinnerAwardTypeId Int,
 @WinnerIsOk Bit,
 @WinnerSinger NVarChar(100),
 @WinnerDate DateTime
)
AS
INSERT INTO dbo.LuckyWinner
(
 [WinnerEmployeeId] ,
 [WinnerAwardTypeId] ,
 [WinnerIsOk] ,
 [WinnerSinger] ,
 [WinnerDate]
)
 VALUES
(
 @WinnerEmployeeId ,
 @WinnerAwardTypeId ,
 @WinnerIsOk ,
 @WinnerSinger ,
 @WinnerDate
)
GO

EXECUTE sp_addextendedproperty N'MS_Description', '获奖者列表增加记录存储过程', N'user', N'dbo', N'procedure', N'proc_Insert_LuckyWinner', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '外鍵,員工ID', N'user', N'dbo', N'procedure', N'proc_Insert_LuckyWinner', N'parameter', N'@WinnerEmployeeId'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '外鍵,獎項類型ID', N'user', N'dbo', N'procedure', N'proc_Insert_LuckyWinner', N'parameter', N'@WinnerAwardTypeId'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '是否簽收确認', N'user', N'dbo', N'procedure', N'proc_Insert_LuckyWinner', N'parameter', N'@WinnerIsOk'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '簽收人(或代理人)', N'user', N'dbo', N'procedure', N'proc_Insert_LuckyWinner', N'parameter', N'@WinnerSinger'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '确認簽收日期', N'user', N'dbo', N'procedure', N'proc_Insert_LuckyWinner', N'parameter', N'@WinnerDate'
GO


IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Insert_LuckyWinnerOutput')
DROP PROCEDURE dbo.proc_Insert_LuckyWinnerOutput
GO
CREATE PROCEDURE dbo.proc_Insert_LuckyWinnerOutput
(
 @WinnerEmployeeId Int,
 @WinnerAwardTypeId Int,
 @WinnerIsOk Bit,
 @WinnerSinger NVarChar(100),
 @WinnerDate DateTime,
 @WinnerId int  output
)
AS
INSERT INTO dbo.LuckyWinner
(
 [WinnerEmployeeId] ,
 [WinnerAwardTypeId] ,
 [WinnerIsOk] ,
 [WinnerSinger] ,
 [WinnerDate]
)
 VALUES
(
 @WinnerEmployeeId ,
 @WinnerAwardTypeId ,
 @WinnerIsOk ,
 @WinnerSinger ,
 @WinnerDate
)
select @WinnerId=@@IDENTITY
GO

EXECUTE sp_addextendedproperty N'MS_Description', '获奖者列表添加记录有返回值的存储过程', N'user', N'dbo', N'procedure', N'proc_Insert_LuckyWinner', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '外鍵,員工ID', N'user', N'dbo', N'procedure', N'proc_Insert_LuckyWinner', N'parameter', N'@WinnerEmployeeId'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '外鍵,獎項類型ID', N'user', N'dbo', N'procedure', N'proc_Insert_LuckyWinner', N'parameter', N'@WinnerAwardTypeId'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '是否簽收确認', N'user', N'dbo', N'procedure', N'proc_Insert_LuckyWinner', N'parameter', N'@WinnerIsOk'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '簽收人(或代理人)', N'user', N'dbo', N'procedure', N'proc_Insert_LuckyWinner', N'parameter', N'@WinnerSinger'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '确認簽收日期', N'user', N'dbo', N'procedure', N'proc_Insert_LuckyWinner', N'parameter', N'@WinnerDate'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '获奖者列表主键输出', N'user', N'dbo', N'procedure', N'proc_Insert_LuckyWinner', N'parameter', N'@WinnerId'
GO

IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Update_LuckyWinner')
DROP PROCEDURE dbo.proc_Update_LuckyWinner
GO
CREATE PROCEDURE dbo.proc_Update_LuckyWinner
(
 @WinnerId Int,
 @WinnerEmployeeId Int,
 @WinnerAwardTypeId Int,
 @WinnerIsOk Bit,
 @WinnerSinger NVarChar(100),
 @WinnerDate DateTime
)
AS
UPDATE dbo.LuckyWinner
 SET
  [WinnerEmployeeId]=@WinnerEmployeeId ,
  [WinnerAwardTypeId]=@WinnerAwardTypeId ,
  [WinnerIsOk]=@WinnerIsOk ,
  [WinnerSinger]=@WinnerSinger ,
  [WinnerDate]=@WinnerDate
 where
  [WinnerId]=@WinnerId
GO

EXECUTE sp_addextendedproperty N'MS_Description', '获奖者列表修改记录的存储过程', N'user', N'dbo', N'procedure', N'proc_Update_LuckyWinner', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '外鍵,員工ID', N'user', N'dbo', N'procedure', N'proc_Update_LuckyWinner', N'parameter', N'@WinnerEmployeeId'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '外鍵,獎項類型ID', N'user', N'dbo', N'procedure', N'proc_Update_LuckyWinner', N'parameter', N'@WinnerAwardTypeId'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '是否簽收确認', N'user', N'dbo', N'procedure', N'proc_Update_LuckyWinner', N'parameter', N'@WinnerIsOk'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '簽收人(或代理人)', N'user', N'dbo', N'procedure', N'proc_Update_LuckyWinner', N'parameter', N'@WinnerSinger'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '确認簽收日期', N'user', N'dbo', N'procedure', N'proc_Update_LuckyWinner', N'parameter', N'@WinnerDate'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '获奖者列表主键', N'user', N'dbo', N'procedure', N'proc_Update_LuckyWinner', N'parameter', N'@WinnerId'
GO

IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Update_LuckyWinnerField')
DROP PROCEDURE dbo.proc_Update_LuckyWinnerField
GO
CREATE PROCEDURE dbo.proc_Update_LuckyWinnerField
(
 @WinnerId Int,
 @FieldValue NVARCHAR(1500)
)
AS
DECLARE @sql NVARCHAR(4000)
SET @sql='update  dbo.LuckyWinner set ' @FieldValue ' WHERE WinnerId ='  CAST(@WinnerId AS VARCHAR(50))
EXEC(@sql)
GO

EXECUTE sp_addextendedproperty N'MS_Description', '获奖者列表修改某一列数据的存储过程', N'user', N'dbo', N'procedure', N'proc_Update_LuckyWinnerField', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '获奖者列表修改字段名及其值如:[Remark]=A', N'user', N'dbo', N'procedure', N'proc_Update_LuckyWinnerField', N'parameter', N'@FieldValue'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '获奖者列表主键', N'user', N'dbo', N'procedure', N'proc_Update_LuckyWinnerField', N'parameter', N'@WinnerId'
GO

IF EXISTS (select * from sysobjects where [name] = 'dbo.proc_Delete_LuckyWinner')
DROP PROCEDURE dbo.proc_Delete_LuckyWinner
GO
CREATE PROCEDURE dbo.proc_Delete_LuckyWinner
(
 @WinnerId Int
)
as
DELETE
 dbo.LuckyWinner
 WHERE
  WinnerId = @WinnerId
GO

EXECUTE sp_addextendedproperty N'MS_Description', '获奖者列表删除记录的存储过程', N'user', N'dbo', N'procedure', N'proc_Delete_LuckyWinner', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '获奖者列表主键', N'user', N'dbo', N'procedure', N'proc_Delete_LuckyWinner', N'parameter', N'@WinnerId'
GO

IF EXISTS (select * from sysobjects where [name] = 'dbo.proc_Delete_LuckyWinnerId')
DROP PROCEDURE dbo.proc_Delete_LuckyWinnerId
GO
CREATE PROCEDURE dbo.proc_Delete_LuckyWinnerId
(
 @WinnerId varchar(2000)
)
as
DECLARE @strsql varchar(3000)
SET @strsql='DELETE dbo.LuckyWinner WHERE WinnerId in(' @WinnerId ')'
EXEC(@strsql)
GO

EXECUTE sp_addextendedproperty N'MS_Description', '获奖者列表删除多条记录的存储过程', N'user', N'dbo', N'procedure', N'proc_Delete_LuckyWinnerId', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '获奖者列表主键', N'user', N'dbo', N'procedure', N'proc_Delete_LuckyWinnerId', N'parameter', N'@WinnerId'
GO

IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Select_LuckyWinner')
DROP PROCEDURE dbo.proc_Select_LuckyWinner
GO
CREATE PROCEDURE dbo.proc_Select_LuckyWinner
(
 @WinnerId Int
)
AS
SELECT * FROM dbo.LuckyWinner WHERE WinnerId = @WinnerId
GO

EXECUTE sp_addextendedproperty N'MS_Description', '获奖者列表查询记录的存储过程', N'user', N'dbo', N'procedure', N'proc_Select_LuckyWinner', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '获奖者列表主键', N'user', N'dbo', N'procedure', N'proc_Select_LuckyWinner', N'parameter', N'@WinnerId'
GO

IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Select_LuckyWinnerExists')
DROP PROCEDURE dbo.proc_Select_LuckyWinnerExists
GO
CREATE PROCEDURE dbo.proc_Select_LuckyWinnerExists
(
 @WinnerId Int
)
AS
SELECT count(1) as H FROM dbo.LuckyWinner WHERE WinnerId = @WinnerId
GO

EXECUTE sp_addextendedproperty N'MS_Description', '获奖者列表是否存在某记录的存储过程', N'user', N'dbo', N'procedure', N'proc_Select_LuckyWinnerExists', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '获奖者列表主键', N'user', N'dbo', N'procedure', N'proc_Select_LuckyWinnerExists', N'parameter', N'@WinnerId'
GO

IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Select_LuckyWinnerCount')
DROP PROCEDURE dbo.proc_Select_LuckyWinnerCount
GO
CREATE PROCEDURE dbo.proc_Select_LuckyWinnerCount
(
 @where NVARCHAR(1000)
)
AS
DECLARE @sql NVARCHAR(4000)
SET @sql='select count(*) as H from dbo.LuckyWinner '
IF @where<>''
 SET @sql=@sql @where
EXEC(@sql)
GO

EXECUTE sp_addextendedproperty N'MS_Description', '获奖者列表共有多少条记录的存储过程', N'user', N'dbo', N'procedure', N'proc_Select_LuckyWinnerCount', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '获奖者列表要模糊查询的字段条件', N'user', N'dbo', N'procedure', N'proc_Select_LuckyWinnerCount', N'parameter', N'@where'
GO

IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Select_LuckyWinnerCountView')
DROP PROCEDURE dbo.proc_Select_LuckyWinnerCountView
GO
CREATE PROCEDURE dbo.proc_Select_LuckyWinnerCountView
(
 @where NVARCHAR(1000)
)
AS
DECLARE @sql NVARCHAR(4000)
SET @sql='select count(*) as H from dbo.View_LuckyWinner '
IF @where<>''
 SET @sql=@sql @where
EXEC(@sql)
GO

EXECUTE sp_addextendedproperty N'MS_Description', '获奖者列表视图查询共有多少条记录的存储过程', N'user', N'dbo', N'procedure', N'proc_Select_LuckyWinnerCountView', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '获奖者列表要模糊查询的字段条件', N'user', N'dbo', N'procedure', N'proc_Select_LuckyWinnerCountView', N'parameter', N'@where'
GO

IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Select_LuckyWinnerTitle')
DROP PROCEDURE dbo.proc_Select_LuckyWinnerTitle
GO
CREATE PROCEDURE dbo.proc_Select_LuckyWinnerTitle
(
 @FieldName NVARCHAR(100),
 @WinnerId Int
)
AS
DECLARE @sql NVARCHAR(4000)
SET @sql='select top 1 ' @FieldName ' from dbo.LuckyWinner WHERE WinnerId ='  CAST(@WinnerId AS VARCHAR(50))
EXEC(@sql)
GO

EXECUTE sp_addextendedproperty N'MS_Description', '获奖者列表返回某字段的标题记录的存储过程', N'user', N'dbo', N'procedure', N'proc_Select_LuckyWinnerTitle', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '获奖者列表要显示的字段名的列表', N'user', N'dbo', N'procedure', N'proc_Select_LuckyWinnerTitle', N'parameter', N'@FieldName'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '获奖者列表主键', N'user', N'dbo', N'procedure', N'proc_Select_LuckyWinnerTitle', N'parameter', N'@WinnerId'
GO

IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Select_LuckyWinnerFuzzySearch')
DROP PROCEDURE dbo.proc_Select_LuckyWinnerFuzzySearch
GO
CREATE PROCEDURE dbo.proc_Select_LuckyWinnerFuzzySearch
(
 @FieldList NVARCHAR(1000),
 @where NVARCHAR(2000)
)
AS
DECLARE @sql NVARCHAR(4000)
IF(@where<>'')
 SET @where=' WHERE ' @where 
IF(@FieldList='')
 SET @FieldList=' * ' 
SET @sql='select ' @FieldList ' from dbo.LuckyWinner ' @where
EXEC(@sql)
GO

EXECUTE sp_addextendedproperty N'MS_Description', '获奖者列表模糊查询记录的存储过程', N'user', N'dbo', N'procedure', N'proc_Select_LuckyWinner', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '获奖者列表要显示的字段名的列表', N'user', N'dbo', N'procedure', N'proc_Select_LuckyWinner', N'parameter', N'@FieldList'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '获奖者列表要模糊查询的字段条件', N'user', N'dbo', N'procedure', N'proc_Select_LuckyWinner', N'parameter', N'@where'
GO

IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Select_LuckyWinnerAll')
DROP PROCEDURE dbo.proc_Select_LuckyWinnerAll
GO
CREATE PROCEDURE dbo.proc_Select_LuckyWinnerAll
AS
SELECT * FROM dbo.LuckyWinner
GO

EXECUTE sp_addextendedproperty N'MS_Description', '获奖者列表查询所有记录存储过程', N'user', N'dbo', N'procedure', N'proc_Select_LuckyWinnerAll', NULL, NULL
GO


IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Insert_WinnerSms')
DROP PROCEDURE dbo.proc_Insert_WinnerSms
GO
CREATE PROCEDURE dbo.proc_Insert_WinnerSms
(
 @SmsEmployeeId Int,
 @SmsContent NVarChar(4000),
 @SmsDate DateTime
)
AS
INSERT INTO dbo.WinnerSms
(
 [SmsEmployeeId] ,
 [SmsContent] ,
 [SmsDate]
)
 VALUES
(
 @SmsEmployeeId ,
 @SmsContent ,
 @SmsDate
)
GO

EXECUTE sp_addextendedproperty N'MS_Description', '所穫獎者發短信表增加记录存储过程', N'user', N'dbo', N'procedure', N'proc_Insert_WinnerSms', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '外鍵,員工ID', N'user', N'dbo', N'procedure', N'proc_Insert_WinnerSms', N'parameter', N'@SmsEmployeeId'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '短信內容', N'user', N'dbo', N'procedure', N'proc_Insert_WinnerSms', N'parameter', N'@SmsContent'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '發送時間', N'user', N'dbo', N'procedure', N'proc_Insert_WinnerSms', N'parameter', N'@SmsDate'
GO


IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Insert_WinnerSmsOutput')
DROP PROCEDURE dbo.proc_Insert_WinnerSmsOutput
GO
CREATE PROCEDURE dbo.proc_Insert_WinnerSmsOutput
(
 @SmsEmployeeId Int,
 @SmsContent NVarChar(4000),
 @SmsDate DateTime,
 @SmsId int  output
)
AS
INSERT INTO dbo.WinnerSms
(
 [SmsEmployeeId] ,
 [SmsContent] ,
 [SmsDate]
)
 VALUES
(
 @SmsEmployeeId ,
 @SmsContent ,
 @SmsDate
)
select @SmsId=@@IDENTITY
GO

EXECUTE sp_addextendedproperty N'MS_Description', '所穫獎者發短信表添加记录有返回值的存储过程', N'user', N'dbo', N'procedure', N'proc_Insert_WinnerSms', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '外鍵,員工ID', N'user', N'dbo', N'procedure', N'proc_Insert_WinnerSms', N'parameter', N'@SmsEmployeeId'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '短信內容', N'user', N'dbo', N'procedure', N'proc_Insert_WinnerSms', N'parameter', N'@SmsContent'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '發送時間', N'user', N'dbo', N'procedure', N'proc_Insert_WinnerSms', N'parameter', N'@SmsDate'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '所穫獎者發短信表主键输出', N'user', N'dbo', N'procedure', N'proc_Insert_WinnerSms', N'parameter', N'@SmsId'
GO

IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Update_WinnerSms')
DROP PROCEDURE dbo.proc_Update_WinnerSms
GO
CREATE PROCEDURE dbo.proc_Update_WinnerSms
(
 @SmsId Int,
 @SmsEmployeeId Int,
 @SmsContent NVarChar(4000),
 @SmsDate DateTime
)
AS
UPDATE dbo.WinnerSms
 SET
  [SmsEmployeeId]=@SmsEmployeeId ,
  [SmsContent]=@SmsContent ,
  [SmsDate]=@SmsDate
 where
  [SmsId]=@SmsId
GO

EXECUTE sp_addextendedproperty N'MS_Description', '所穫獎者發短信表修改记录的存储过程', N'user', N'dbo', N'procedure', N'proc_Update_WinnerSms', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '外鍵,員工ID', N'user', N'dbo', N'procedure', N'proc_Update_WinnerSms', N'parameter', N'@SmsEmployeeId'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '短信內容', N'user', N'dbo', N'procedure', N'proc_Update_WinnerSms', N'parameter', N'@SmsContent'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '發送時間', N'user', N'dbo', N'procedure', N'proc_Update_WinnerSms', N'parameter', N'@SmsDate'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '所穫獎者發短信表主键', N'user', N'dbo', N'procedure', N'proc_Update_WinnerSms', N'parameter', N'@SmsId'
GO

IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Update_WinnerSmsField')
DROP PROCEDURE dbo.proc_Update_WinnerSmsField
GO
CREATE PROCEDURE dbo.proc_Update_WinnerSmsField
(
 @SmsId Int,
 @FieldValue NVARCHAR(1500)
)
AS
DECLARE @sql NVARCHAR(4000)
SET @sql='update  dbo.WinnerSms set ' @FieldValue ' WHERE SmsId ='  CAST(@SmsId AS VARCHAR(50))
EXEC(@sql)
GO

EXECUTE sp_addextendedproperty N'MS_Description', '所穫獎者發短信表修改某一列数据的存储过程', N'user', N'dbo', N'procedure', N'proc_Update_WinnerSmsField', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '所穫獎者發短信表修改字段名及其值如:[Remark]=A', N'user', N'dbo', N'procedure', N'proc_Update_WinnerSmsField', N'parameter', N'@FieldValue'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '所穫獎者發短信表主键', N'user', N'dbo', N'procedure', N'proc_Update_WinnerSmsField', N'parameter', N'@SmsId'
GO

IF EXISTS (select * from sysobjects where [name] = 'dbo.proc_Delete_WinnerSms')
DROP PROCEDURE dbo.proc_Delete_WinnerSms
GO
CREATE PROCEDURE dbo.proc_Delete_WinnerSms
(
 @SmsId Int
)
as
DELETE
 dbo.WinnerSms
 WHERE
  SmsId = @SmsId
GO

EXECUTE sp_addextendedproperty N'MS_Description', '所穫獎者發短信表删除记录的存储过程', N'user', N'dbo', N'procedure', N'proc_Delete_WinnerSms', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '所穫獎者發短信表主键', N'user', N'dbo', N'procedure', N'proc_Delete_WinnerSms', N'parameter', N'@SmsId'
GO

IF EXISTS (select * from sysobjects where [name] = 'dbo.proc_Delete_WinnerSmsId')
DROP PROCEDURE dbo.proc_Delete_WinnerSmsId
GO
CREATE PROCEDURE dbo.proc_Delete_WinnerSmsId
(
 @SmsId varchar(2000)
)
as
DECLARE @strsql varchar(3000)
SET @strsql='DELETE dbo.WinnerSms WHERE SmsId in(' @SmsId ')'
EXEC(@strsql)
GO

EXECUTE sp_addextendedproperty N'MS_Description', '所穫獎者發短信表删除多条记录的存储过程', N'user', N'dbo', N'procedure', N'proc_Delete_WinnerSmsId', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '所穫獎者發短信表主键', N'user', N'dbo', N'procedure', N'proc_Delete_WinnerSmsId', N'parameter', N'@SmsId'
GO

IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Select_WinnerSms')
DROP PROCEDURE dbo.proc_Select_WinnerSms
GO
CREATE PROCEDURE dbo.proc_Select_WinnerSms
(
 @SmsId Int
)
AS
SELECT * FROM dbo.WinnerSms WHERE SmsId = @SmsId
GO

EXECUTE sp_addextendedproperty N'MS_Description', '所穫獎者發短信表查询记录的存储过程', N'user', N'dbo', N'procedure', N'proc_Select_WinnerSms', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '所穫獎者發短信表主键', N'user', N'dbo', N'procedure', N'proc_Select_WinnerSms', N'parameter', N'@SmsId'
GO

IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Select_WinnerSmsExists')
DROP PROCEDURE dbo.proc_Select_WinnerSmsExists
GO
CREATE PROCEDURE dbo.proc_Select_WinnerSmsExists
(
 @SmsId Int
)
AS
SELECT count(1) as H FROM dbo.WinnerSms WHERE SmsId = @SmsId
GO

EXECUTE sp_addextendedproperty N'MS_Description', '所穫獎者發短信表是否存在某记录的存储过程', N'user', N'dbo', N'procedure', N'proc_Select_WinnerSmsExists', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '所穫獎者發短信表主键', N'user', N'dbo', N'procedure', N'proc_Select_WinnerSmsExists', N'parameter', N'@SmsId'
GO

IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Select_WinnerSmsCount')
DROP PROCEDURE dbo.proc_Select_WinnerSmsCount
GO
CREATE PROCEDURE dbo.proc_Select_WinnerSmsCount
(
 @where NVARCHAR(1000)
)
AS
DECLARE @sql NVARCHAR(4000)
SET @sql='select count(*) as H from dbo.WinnerSms '
IF @where<>''
 SET @sql=@sql @where
EXEC(@sql)
GO

EXECUTE sp_addextendedproperty N'MS_Description', '所穫獎者發短信表共有多少条记录的存储过程', N'user', N'dbo', N'procedure', N'proc_Select_WinnerSmsCount', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '所穫獎者發短信表要模糊查询的字段条件', N'user', N'dbo', N'procedure', N'proc_Select_WinnerSmsCount', N'parameter', N'@where'
GO

IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Select_WinnerSmsCountView')
DROP PROCEDURE dbo.proc_Select_WinnerSmsCountView
GO
CREATE PROCEDURE dbo.proc_Select_WinnerSmsCountView
(
 @where NVARCHAR(1000)
)
AS
DECLARE @sql NVARCHAR(4000)
SET @sql='select count(*) as H from dbo.View_WinnerSms '
IF @where<>''
 SET @sql=@sql @where
EXEC(@sql)
GO

EXECUTE sp_addextendedproperty N'MS_Description', '所穫獎者發短信表视图查询共有多少条记录的存储过程', N'user', N'dbo', N'procedure', N'proc_Select_WinnerSmsCountView', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '所穫獎者發短信表要模糊查询的字段条件', N'user', N'dbo', N'procedure', N'proc_Select_WinnerSmsCountView', N'parameter', N'@where'
GO

IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Select_WinnerSmsTitle')
DROP PROCEDURE dbo.proc_Select_WinnerSmsTitle
GO
CREATE PROCEDURE dbo.proc_Select_WinnerSmsTitle
(
 @FieldName NVARCHAR(100),
 @SmsId Int
)
AS
DECLARE @sql NVARCHAR(4000)
SET @sql='select top 1 ' @FieldName ' from dbo.WinnerSms WHERE SmsId ='  CAST(@SmsId AS VARCHAR(50))
EXEC(@sql)
GO

EXECUTE sp_addextendedproperty N'MS_Description', '所穫獎者發短信表返回某字段的标题记录的存储过程', N'user', N'dbo', N'procedure', N'proc_Select_WinnerSmsTitle', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '所穫獎者發短信表要显示的字段名的列表', N'user', N'dbo', N'procedure', N'proc_Select_WinnerSmsTitle', N'parameter', N'@FieldName'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '所穫獎者發短信表主键', N'user', N'dbo', N'procedure', N'proc_Select_WinnerSmsTitle', N'parameter', N'@SmsId'
GO

IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Select_WinnerSmsFuzzySearch')
DROP PROCEDURE dbo.proc_Select_WinnerSmsFuzzySearch
GO
CREATE PROCEDURE dbo.proc_Select_WinnerSmsFuzzySearch
(
 @FieldList NVARCHAR(1000),
 @where NVARCHAR(2000)
)
AS
DECLARE @sql NVARCHAR(4000)
IF(@where<>'')
 SET @where=' WHERE ' @where 
IF(@FieldList='')
 SET @FieldList=' * ' 
SET @sql='select ' @FieldList ' from dbo.WinnerSms ' @where
EXEC(@sql)
GO

EXECUTE sp_addextendedproperty N'MS_Description', '所穫獎者發短信表模糊查询记录的存储过程', N'user', N'dbo', N'procedure', N'proc_Select_WinnerSms', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '所穫獎者發短信表要显示的字段名的列表', N'user', N'dbo', N'procedure', N'proc_Select_WinnerSms', N'parameter', N'@FieldList'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '所穫獎者發短信表要模糊查询的字段条件', N'user', N'dbo', N'procedure', N'proc_Select_WinnerSms', N'parameter', N'@where'
GO

IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Select_WinnerSmsAll')
DROP PROCEDURE dbo.proc_Select_WinnerSmsAll
GO
CREATE PROCEDURE dbo.proc_Select_WinnerSmsAll
AS
SELECT * FROM dbo.WinnerSms
GO

EXECUTE sp_addextendedproperty N'MS_Description', '所穫獎者發短信表查询所有记录存储过程', N'user', N'dbo', N'procedure', N'proc_Select_WinnerSmsAll', NULL, NULL
GO


IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Insert_FormBackground')
DROP PROCEDURE dbo.proc_Insert_FormBackground
GO
CREATE PROCEDURE dbo.proc_Insert_FormBackground
(
 @BackgroundImage Image,
 @BackgroundFmName NVarChar(400)
)
 AS
INSERT INTO dbo.FormBackground
(
 [BackgroundImage] ,
 [BackgroundFmName]
)
 VALUES
(
 @BackgroundImage ,
 @BackgroundFmName
)
GO

EXECUTE sp_addextendedproperty N'MS_Description', '窗口背景圖表增加记录存储过程', N'user', N'dbo', N'procedure', N'proc_Insert_FormBackground', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '背景圖', N'user', N'dbo', N'procedure', N'proc_Insert_FormBackground', N'parameter', N'@BackgroundImage'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '窗口名稱', N'user', N'dbo', N'procedure', N'proc_Insert_FormBackground', N'parameter', N'@BackgroundFmName'
GO


IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Insert_FormBackgroundOutput')
DROP PROCEDURE dbo.proc_Insert_FormBackgroundOutput
GO
CREATE PROCEDURE dbo.proc_Insert_FormBackgroundOutput
(
 @BackgroundImage Image,
 @BackgroundFmName NVarChar(400),

  @BackgroundId int  output
)
AS
INSERT INTO dbo.FormBackground
(
 [BackgroundImage] ,
 [BackgroundFmName]
)
 VALUES
(
 @BackgroundImage ,
 @BackgroundFmName
)
select @BackgroundId=@@IDENTITY
GO

EXECUTE sp_addextendedproperty N'MS_Description', '窗口背景圖表添加记录有返回值的存储过程', N'user', N'dbo', N'procedure', N'proc_Insert_FormBackground', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '背景圖', N'user', N'dbo', N'procedure', N'proc_Insert_FormBackground', N'parameter', N'@BackgroundImage'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '窗口名稱', N'user', N'dbo', N'procedure', N'proc_Insert_FormBackground', N'parameter', N'@BackgroundFmName'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '窗口背景圖表主键输出', N'user', N'dbo', N'procedure', N'proc_Insert_FormBackground', N'parameter', N'@BackgroundId'
GO

IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Update_FormBackground')
DROP PROCEDURE dbo.proc_Update_FormBackground
GO
CREATE PROCEDURE dbo.proc_Update_FormBackground
(
 @BackgroundId Int,
 @BackgroundImage Image,
 @BackgroundFmName NVarChar(400)
)
AS
UPDATE dbo.FormBackground
 SET
  [BackgroundImage]=@BackgroundImage ,
  [BackgroundFmName]=@BackgroundFmName
 where
  [BackgroundId]=@BackgroundId
GO

EXECUTE sp_addextendedproperty N'MS_Description', '窗口背景圖表修改记录的存储过程', N'user', N'dbo', N'procedure', N'proc_Update_FormBackground', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '背景圖', N'user', N'dbo', N'procedure', N'proc_Update_FormBackground', N'parameter', N'@BackgroundImage'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '窗口名稱', N'user', N'dbo', N'procedure', N'proc_Update_FormBackground', N'parameter', N'@BackgroundFmName'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '窗口背景圖表主键', N'user', N'dbo', N'procedure', N'proc_Update_FormBackground', N'parameter', N'@BackgroundId'
GO

IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Update_FormBackgroundField')
DROP PROCEDURE dbo.proc_Update_FormBackgroundField
GO
CREATE PROCEDURE dbo.proc_Update_FormBackgroundField
(
 @BackgroundId Int,
 @FieldValue NVARCHAR(1500)
)
AS
DECLARE @sql NVARCHAR(4000)
SET @sql='update  dbo.FormBackground set ' @FieldValue ' WHERE BackgroundId ='  CAST(@BackgroundId AS VARCHAR(50))
EXEC(@sql)
GO

EXECUTE sp_addextendedproperty N'MS_Description', '窗口背景圖表修改某一列数据的存储过程', N'user', N'dbo', N'procedure', N'proc_Update_FormBackgroundField', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '窗口背景圖表修改字段名及其值如:[Remark]=A', N'user', N'dbo', N'procedure', N'proc_Update_FormBackgroundField', N'parameter', N'@FieldValue'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '窗口背景圖表主键', N'user', N'dbo', N'procedure', N'proc_Update_FormBackgroundField', N'parameter', N'@BackgroundId'
GO

IF EXISTS (select * from sysobjects where [name] = 'dbo.proc_Delete_FormBackground')
DROP PROCEDURE dbo.proc_Delete_FormBackground
GO
CREATE PROCEDURE dbo.proc_Delete_FormBackground
(
 @BackgroundId Int
)
as
DELETE
 dbo.FormBackground
 WHERE
  BackgroundId = @BackgroundId
GO

EXECUTE sp_addextendedproperty N'MS_Description', '窗口背景圖表删除记录的存储过程', N'user', N'dbo', N'procedure', N'proc_Delete_FormBackground', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '窗口背景圖表主键', N'user', N'dbo', N'procedure', N'proc_Delete_FormBackground', N'parameter', N'@BackgroundId'
GO

IF EXISTS (select * from sysobjects where [name] = 'dbo.proc_Delete_FormBackgroundId')
DROP PROCEDURE dbo.proc_Delete_FormBackgroundId
GO
CREATE PROCEDURE dbo.proc_Delete_FormBackgroundId
(
 @BackgroundId varchar(2000)
)
as
DECLARE @strsql varchar(3000)
SET @strsql='DELETE dbo.FormBackground WHERE BackgroundId in(' @BackgroundId ')'
EXEC(@strsql)
GO

EXECUTE sp_addextendedproperty N'MS_Description', '窗口背景圖表删除多条记录的存储过程', N'user', N'dbo', N'procedure', N'proc_Delete_FormBackgroundId', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '窗口背景圖表主键', N'user', N'dbo', N'procedure', N'proc_Delete_FormBackgroundId', N'parameter', N'@BackgroundId'
GO

IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Select_FormBackground')
DROP PROCEDURE dbo.proc_Select_FormBackground
GO
CREATE PROCEDURE dbo.proc_Select_FormBackground
(
 @BackgroundId Int
)
AS
SELECT * FROM dbo.FormBackground WHERE BackgroundId = @BackgroundId
GO

EXECUTE sp_addextendedproperty N'MS_Description', '窗口背景圖表查询记录的存储过程', N'user', N'dbo', N'procedure', N'proc_Select_FormBackground', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '窗口背景圖表主键', N'user', N'dbo', N'procedure', N'proc_Select_FormBackground', N'parameter', N'@BackgroundId'
GO

IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Select_FormBackgroundExists')
DROP PROCEDURE dbo.proc_Select_FormBackgroundExists
GO
CREATE PROCEDURE dbo.proc_Select_FormBackgroundExists
(
 @BackgroundId Int
)
AS
SELECT count(1) as H FROM dbo.FormBackground WHERE BackgroundId = @BackgroundId
GO

EXECUTE sp_addextendedproperty N'MS_Description', '窗口背景圖表是否存在某记录的存储过程', N'user', N'dbo', N'procedure', N'proc_Select_FormBackgroundExists', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '窗口背景圖表主键', N'user', N'dbo', N'procedure', N'proc_Select_FormBackgroundExists', N'parameter', N'@BackgroundId'
GO

IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Select_FormBackgroundCount')
DROP PROCEDURE dbo.proc_Select_FormBackgroundCount
GO
CREATE PROCEDURE dbo.proc_Select_FormBackgroundCount
(
 @where NVARCHAR(1000)
)
AS
DECLARE @sql NVARCHAR(4000)
SET @sql='select count(*) as H from dbo.FormBackground '
IF @where<>''
 SET @sql=@sql @where
EXEC(@sql)
GO

EXECUTE sp_addextendedproperty N'MS_Description', '窗口背景圖表共有多少条记录的存储过程', N'user', N'dbo', N'procedure', N'proc_Select_FormBackgroundCount', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '窗口背景圖表要模糊查询的字段条件', N'user', N'dbo', N'procedure', N'proc_Select_FormBackgroundCount', N'parameter', N'@where'
GO

IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Select_FormBackgroundCountView')
DROP PROCEDURE dbo.proc_Select_FormBackgroundCountView
GO
CREATE PROCEDURE dbo.proc_Select_FormBackgroundCountView
(
 @where NVARCHAR(1000)
)
AS
DECLARE @sql NVARCHAR(4000)
SET @sql='select count(*) as H from dbo.View_FormBackground '
IF @where<>''
 SET @sql=@sql @where
EXEC(@sql)
GO

EXECUTE sp_addextendedproperty N'MS_Description', '窗口背景圖表视图查询共有多少条记录的存储过程', N'user', N'dbo', N'procedure', N'proc_Select_FormBackgroundCountView', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '窗口背景圖表要模糊查询的字段条件', N'user', N'dbo', N'procedure', N'proc_Select_FormBackgroundCountView', N'parameter', N'@where'
GO

IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Select_FormBackgroundTitle')
DROP PROCEDURE dbo.proc_Select_FormBackgroundTitle
GO
CREATE PROCEDURE dbo.proc_Select_FormBackgroundTitle
(
 @FieldName NVARCHAR(100),
 @BackgroundId Int
)
AS
DECLARE @sql NVARCHAR(4000)
SET @sql='select top 1 ' @FieldName ' from dbo.FormBackground WHERE BackgroundId ='  CAST(@BackgroundId AS VARCHAR(50))
EXEC(@sql)
GO

EXECUTE sp_addextendedproperty N'MS_Description', '窗口背景圖表返回某字段的标题记录的存储过程', N'user', N'dbo', N'procedure', N'proc_Select_FormBackgroundTitle', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '窗口背景圖表要显示的字段名的列表', N'user', N'dbo', N'procedure', N'proc_Select_FormBackgroundTitle', N'parameter', N'@FieldName'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '窗口背景圖表主键', N'user', N'dbo', N'procedure', N'proc_Select_FormBackgroundTitle', N'parameter', N'@BackgroundId'
GO

IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Select_FormBackgroundFuzzySearch')
DROP PROCEDURE dbo.proc_Select_FormBackgroundFuzzySearch
GO
CREATE PROCEDURE dbo.proc_Select_FormBackgroundFuzzySearch
(
 @FieldList NVARCHAR(1000),
 @where NVARCHAR(2000)
)
AS
DECLARE @sql NVARCHAR(4000)
IF(@where<>'')
 SET @where=' WHERE ' @where 
IF(@FieldList='')
 SET @FieldList=' * ' 
SET @sql='select ' @FieldList ' from dbo.FormBackground ' @where
EXEC(@sql)
GO

EXECUTE sp_addextendedproperty N'MS_Description', '窗口背景圖表模糊查询记录的存储过程', N'user', N'dbo', N'procedure', N'proc_Select_FormBackground', NULL, NULL
GO

EXECUTE sp_addextendedproperty N'MS_Description', '窗口背景圖表要显示的字段名的列表', N'user', N'dbo', N'procedure', N'proc_Select_FormBackground', N'parameter', N'@FieldList'
GO

EXECUTE sp_addextendedproperty N'MS_Description', '窗口背景圖表要模糊查询的字段条件', N'user', N'dbo', N'procedure', N'proc_Select_FormBackground', N'parameter', N'@where'
GO

IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'dbo.proc_Select_FormBackgroundAll')
DROP PROCEDURE dbo.proc_Select_FormBackgroundAll
GO
CREATE PROCEDURE dbo.proc_Select_FormBackgroundAll
AS
SELECT * FROM dbo.FormBackground
GO

EXECUTE sp_addextendedproperty N'MS_Description', '窗口背景圖表查询所有记录存储过程', N'user', N'dbo', N'procedure', N'proc_Select_FormBackgroundAll', NULL, NULL
GO

消除思路

  1. 删除原有自动生成主键触发器
  2. 双重创建新的系列生成器
  3. 再一次创造主键生成触发器

    存储大小:基本上都以int类型,要求占用4byte,挂念主健索引的情状下,每一个主健基本上要据有8byte的寄存空间。
    优点:
          1. 中坚由数据库自动生成,不重复,无须手动维护。
          2. 仓库储存空间小,假若数据库设计合理,每页记录能够累积更多行记录,减少IO读写。
          3. 品质好,无需思考并发,事务等有关影响属性的主题素材,系统活动管理。
   缺点:
          1. 字长独有31位,在Web下海量数据存款和储蓄时,三11位ID理论上存在相当不足用的情况。
          2. 数据库集群情形下,或许在数量移植或合併进度中,八个记录可能存在主健重复的景色,后果很严重!
          3. 固然数额操作是大旨操作时,存在相互引用的涉嫌,必得操作N次才方可保留成功(N > 1 ,不思考存储进程气象下)
          4. 由于是数据自动生成,会存在ID浪费的状态,比方批量引进1W条记下,则ID相应增大1W,当事情败北回滚时,此ID操作不会回滚,当前ID将变为ID 1W

  自动生成实体(老版的)未考虑暗中认可值

索求最大主键

SELECT MAX(主键字段) FROM 表名称;

2. GUID
    有三种GUID,一种数据库生成,一种程序生成,GUID将侵占16byte,思量索引要霸占32byte的长空
    优点:
          1. 三种生成方式,数据不重复,无须手动维护。
          2. 数量统一移植进程中不用思念主健ID重复因素 。
          3. 主导操作程序中,能够程序贰次性 生成相关GUID,提交程序质量
   缺点:
          1. GUID占用16byte,怀念索引等唇亡齿寒情况,严重浪费数据库存款和储蓄空间。
          2. 询问质量相对比较慢,每便保存时候必要再行排序索引,索引质量也拥有回降。
          3. 采取RAID存储时, 在品质上还会有贰个分别因素. GUID方案的读写磁头和区域是相仿随机分配的, 而identity方案则是集中在叁个区块的。

 /// <summary>
 /// 獎項項目表AwardProject表的实体类
 ///生成時間2018/4/27 16:14:19
 ///塗聚文(Geovin Du)
 ///</summary>
 public class AwardProjectInfo
 {
  private int _ProjectId;

  ///<summary>
  /// 項目ID;
  ///</summary>
  public int ProjectId
  {
   get { return _ProjectId; }
   set {_ProjectId = value; }
  }

  private string _ProjectName;

  ///<summary>
  /// 項目名稱,顯示年度年會上;
  ///</summary>
  public string ProjectName
  {
   get { return _ProjectName; }
   set {_ProjectName = value; }
  }

  private string _ProjectYear;

  ///<summary>
  /// 年度名稱;
  ///</summary>
  public string ProjectYear
  {
   get { return _ProjectYear; }
   set {_ProjectYear = value; }
  }

  private DateTime _ProjectDate;

  ///<summary>
  /// 創建日期;
  ///</summary>
  public DateTime ProjectDate
  {
   get { return _ProjectDate; }
   set {_ProjectDate = value; }
  }

  private List<EmployeeInfo> _Employee;

  /// <summary>
  /// 主表:AwardProject獎項項目表,外键表:Employee抽獎員工表,外键字段:EmployeeProjectId;
  ///</summary>
  public  List<EmployeeInfo> EmployeeList 
  {
   get { return _Employee; }
   set {_Employee = value; }
  }

  private DataTable _EmployeeData;

  /// <summary>
  /// 主表:AwardProject獎項項目表,外键表:Employee抽獎員工表,外键字段:EmployeeProjectId;
  ///</summary>
  public  DataTable EmployeeData 
  {
   get { return _EmployeeData; }
   set {_EmployeeData = value; }
  }

  private DataSet _EmployeeDaset;

  /// <summary>
  /// 主表:AwardProject獎項項目表,外键表:Employee抽獎員工表,外键字段:EmployeeProjectId;
  ///</summary>
  public  DataSet EmployeeDaset 
  {
   get { return _EmployeeDaset; }
   set {_EmployeeDaset = value; }
  }

  private List<AwardTypeInfo> _AwardType;

  /// <summary>
  /// 主表:AwardProject獎項項目表,外键表:AwardType奖项表 ,外键字段:AwardProjectId;
  ///</summary>
  public  List<AwardTypeInfo> AwardTypeList 
  {
   get { return _AwardType; }
   set {_AwardType = value; }
  }

  private DataTable _AwardTypeData;

  /// <summary>
  /// 主表:AwardProject獎項項目表,外键表:AwardType奖项表 ,外键字段:AwardProjectId;
  ///</summary>
  public  DataTable AwardTypeData 
  {
   get { return _AwardTypeData; }
   set {_AwardTypeData = value; }
  }

  private DataSet _AwardTypeDaset;

  /// <summary>
  /// 主表:AwardProject獎項項目表,外键表:AwardType奖项表 ,外键字段:AwardProjectId;
  ///</summary>
  public  DataSet AwardTypeDaset 
  {
   get { return _AwardTypeDaset; }
   set {_AwardTypeDaset = value; }
  }


 }

删去旧触发器

DROP TRIGGER 触发器名称;

3. 数据库MaxID算法
    存储字节可4-8个字节,自身定义,自由。可针对各种事情对象(表)都有二个最后马克斯ID,此算法能够看作是Identity 变种。
    优点:
          1. 手工业调整ID生成,存款和储蓄空间,索引大小自身说了算
          2. 优先能够怀恋数据统一ID重复难题,如数据库最高级中学一年级个人作为目的分别,如库1最高位1,数据库2高高的位2 ,以次类推,数据统一无须思量数据再一次难点
    缺点:
          1. 不管是主从表存款和储蓄,照旧单表存款和储蓄,都亟需先去数据取三回ID,质量差。
          2. 得到马克斯ID时候,尽管未和保留操作放置到多个政工里,假若更新覆盖,如A发生的是100,B也会产生100,变成地下数据失实。
          3. 停放贰个作业里管理不佳,轻巧死锁。标准先查最大值马克斯ID,然后更新为MaxID 1,并发中五个线程同期对马克斯ID加分享锁,最后升任更新锁失利,发生死锁。

  

开立异队列

CREATE
    SEQUENCE seq_third_app_permission MINVALUE 1 START WITH 301 --从301开始
    INCREMENT BY 1 --不设最大值
    NOMAXVALUE NOCYCLE --不循环
    NOCACHE;

Note:此处的301队列领头值,是由「查找最大主键」的询问结果基础方面加一收获的,这一步十分重大。

4. 程序ID生成器算法

成立新触发器

CREATE TRIGGER 触发器名称   
BEFORE INSERT
ON APPDB.table_name -- 表名   
FOR EACH ROW  
BEGIN  
  SELECT seq_third_app_permission.nextval   
  INTO :new.主键字段 -- :new这个部分时INSERT语句使用的语法
  FROM DUAL;
END;

    存款和储蓄字节可4-14个字节,本身定义,自由。算法选取性多

总结:

当表未有明了的独一约束的时候,就要思索是或不是表中主键字段自动生成影响了独一约束标准。

参考:
ORA-00001: 违反独一约束原则

    优点同上:
          3. 自身能够安排叁个更快捷的ID生成算法
    缺点:
          1. 万一将ID计算结果存款和储蓄在内部存款和储蓄器,恐怕File中要求思索程序的可相信性。
          2. 先后生成进程中,必需惦记并发难题,加锁等休戚相关管理情势
          3. 最大值保慰难题,假诺保留在数据库将面对上述3的难题,假设保留在内部存款和储蓄器中可能file中,算法生成器失败后,怎么器重新起头最中号改为难点.思考品质和别的相关主题素材,能够每间距生              成1000个ID更新贰遍数据库,有生成器统一管理。

本文由星彩网app下载发布于星彩彩票app下载,转载请注明出处:系统架构划设想计之,怎么样根据一定法规自动

TAG标签:
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。