SQL Server: create table sql script

---摇奖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

　　根据以上的代码生成的表，自动生成的简单的添加、删除、修改、查询的存储过程（关联还没有考虑，可以考虑进去）

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

　　自动生成实体（老版的）未考虑默认值

	/// <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; }

		}

	}

秒客网

SQL Server: create table sql script

相关文章