SQL Server: create table sql script

来源:https://www.cnblogs.com/geovindu/archive/2018/04/27/8961688.html
-Advertisement-
Play Games

根據以上的代碼生成的表，自動生成的簡單的添加、刪除、修改、查詢的存儲過程（關聯還沒有考慮，可以考慮進去） 自動生成實體（老版的）未考慮預設值 ...


---搖獎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'd
您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 中斷標誌位 IRQF_ONESHOT
    one shot本身的意思的只有一次的，結合到中斷這個場景，則表示中斷是一次性觸發的，不能嵌套。對於primary handler，當然是不會嵌套，但是對於threaded interrupt handler，我們有兩種選擇，一種是mask該interrupt source，另外一種是unmask該 ...
  • Redis學習-常用命令
    keys * 返回滿足的所有鍵 exists key 是否存在指定的key，存在返回1，不存在返回0 expire key time 設置指定key的過期時間，可以使用ttl key查看剩餘時間 persist key 取消過期時間 select db 選擇資料庫，資料庫為0-15（一共16個資料庫 ...
  • [翻譯]:MySQL Error: Too many connections
    翻譯:MySQL Error: Too many connections 前言： 本文是對Muhammad Irfan的這篇博客MySQL Error: Too many connections的翻譯,如有翻譯不對或不好的地方，敬請指出，大家一起學習進步。尊重原創和翻譯勞動成果，轉載時請註明出處。謝... ...
  • mssql sqlerver 腳本 計算數據表的結餘數的方法分享
    轉自：http://www.maomao365.com/?p=5710 摘要:今天接到一個需求,有一張數據表，記錄的是消費明細數據，現在需要做一個累計結餘，記錄每次的數據結餘合計,下文將展示一種sql腳本的編寫方式實驗環境:sqlserver 2008 R2如下例所示: ...
  • Oracle表分區分為四種：範圍分區，散列分區，列表分區和複合分區（轉載）
    一：範圍分區 就是根據資料庫表中某一欄位的值的範圍來劃分分區，例如： 插入實驗數據： 下麵查詢一下全部數據，然後查詢各個分區數據，代碼一起寫： 全部數據如下： 不及格數據如下： 及格數據如下： 優秀數據如下： 說明：數據中有空值，Oracle機制會自動將其規划到maxvalue的分區中。 二：散列分 ...
  • MySQL5.7.21啟動異常的修複
    一、問題： 一同事反饋有一MySQL實例因為斷電之後，啟動不了。用了innodb_force_recovery=6也無效，於是前往查看。 二、排查過程： 最早的啟動信息裡面，沒有任何報錯，只有一行[ERROR] Aborting提示，如下： 接著同事用了innodb_force_recovery＝6 ...
  • redis緩存資料庫入門教程
    入門redis教程 前言： 應公司需求，最近學習了一下redis資料庫的一些簡單入門的教程，整理出來分享給大家，喜歡的可以關註和點贊哦~ 如文章中有不足之處求指正，謝謝 目錄 ·什麼是redis？為什麼用使用它？ ·redis和mysql的區別？ ·適用的場景 ·redis的安裝以及使用？ ·常用的 ...
  • zookeeper工作機制
    Zookeeper概念簡介： Zookeeper是為用戶的分散式應用程式提供協調服務的 zookeeper是為別的分散式程式服務的 Zookeeper本身就是一個分散式程式（只要有半數以上節點存活，zk就能正常服務） Zookeeper所提供的服務涵蓋：主從協調、伺服器節點動態上下線、統一配置管理、 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類