Dunk

-- output
-- http://msdn.microsoft.com/zh-tw/library/ms177564.aspx
-- 隨機取出資料行
-- http://plus-now.com/?p=208
USE TESTDB
GO
-- 建立使用者定義表格資料欄位(公告欄)(欄位定義同表格dbo.Billboard)
if exists
(SELECT name
 FROM
	 sys.types
 WHERE
	 name = 'BillboardType') DROP TYPE dbo.BillboardType
CREATE TYPE dbo.BillboardType AS TABLE
(
[IDENTITY_FIELD] int NOT NULL,
	[oldUSR_ID] [nvarchar](20) NULL,
	[USR_ID] [nvarchar](20) NULL,
	[Title] [nvarchar](2000) NULL,
	[Content] [nvarchar](4000) NULL,
	[AttachName] [nvarchar](200) NULL,
	[CreateDate] [datetime] NULL,
	[UpdateDate] [datetime] NULL,
	[ExpireDate] [char](10) NULL,
	[IsSign] [char](1) NULL
);
GO
-- 建立初始資料
TRUNCATE TABLE dbo.Billboard
GO

DECLARE @counter int
SET @counter = 10;

WHILE @counter >0
BEGIN
INSERT INTO [TESTDB].[dbo].[Billboard] ([oldUSR_ID]
									   , [USR_ID]
									   , [Title]
									   , [Content]
									   , [AttachName]
									   , [CreateDate]
									   , [UpdateDate]
									   , [ExpireDate]
									   , [IsSign])
VALUES
	('123456', '123456', '測試標題' + convert(VARCHAR, getdate(), 9), '1', NULL, getdate(), getdate(), dateadd(yyyy, 1, getdate()), '0')
SELECT @counter = @counter - 1;
END

SELECT *
FROM
	dbo.Billboard
GO		

-- insert
DECLARE @billboardtemp BillboardType;

INSERT INTO [TESTDB].[dbo].[Billboard] ([oldUSR_ID]
									   , [USR_ID]
									   , [Title]
									   , [Content]
									   , [AttachName]
									   , [CreateDate]
									   , [UpdateDate]
									   , [ExpireDate]
									   , [IsSign])
OUTPUT
	INSERTED.* INTO @billboardtemp
VALUES
	('123456', '123456', '測試標題' + convert(VARCHAR, getdate(), 9), '1', NULL, getdate(), getdate(), dateadd(yyyy, 1, getdate()), '0')

SELECT *
FROM
	@billboardtemp
GO

-- delete
DECLARE @billboardtemp BillboardType;
DECLARE @idenfield int
SELECT @idenfield = max(IDENTITY_FIELD)
FROM
	dbo.Billboard

DELETE
FROM
	dbo.Billboard
OUTPUT
	DELETED.* INTO @billboardtemp
WHERE
	IDENTITY_FIELD = @idenfield

SELECT *
FROM
	@billboardtemp
GO

-- update
-- 隨機選擇資料行
-- SELECT top 1 IDENTITY_FIELD from dbo.Billboard order by NEWID()
DECLARE @billboardModifyContent table
(
  IDENTITY_FIELD int,
  BeforeContent nvarchar(4000),	
  AfterContent nvarchar(4000),
  ModifyDate datetime
)


UPDATE dbo.Billboard
SET
	Title = N'修改後標題' + convert(VARCHAR, getdate(), 9)
OUTPUT
	INSERTED.IDENTITY_FIELD, DELETED.Title, INSERTED.Title, getdate() INTO @billboardModifyContent
WHERE
	IDENTITY_FIELD = (SELECT TOP 1 IDENTITY_FIELD
					  FROM
						  dbo.Billboard
					  ORDER BY
						  newid())

SELECT *
FROM
	@billboardModifyContent
GO

-- 檢視所有資料
SELECT *
FROM
	dbo.Billboard
GO