install.sql

来自「FAQ-Modified for Rainbow」· SQL 代码 · 共 115 行

/* Install script, FAQs module, Jakob Hansen, 25 april 2003 */

if Not exists (select * from dbo.sysobjects where id = object_id(N'[rb_FAQs]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
    CREATE TABLE [rb_FAQs] (
	[ItemID] [int] IDENTITY (1, 1) NOT NULL ,
	[ModuleID] [int] NOT NULL ,
	[CreatedByUser] [nvarchar] (100) NULL ,
	[CreatedDate] [datetime] NULL ,
	[Question] [nvarchar] (500) NULL ,
	[Answer] [nvarchar] (4000) NULL 
    ) ON [PRIMARY] 
END
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[rb_AddFAQ]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [rb_AddFAQ]
GO
CREATE PROCEDURE [rb_AddFAQ]
	(@ItemID 	[int] OUTPUT,
	 @ModuleID 	[int],
	 @UserName	[nvarchar] (100),
	 @Question 	[nvarchar] (500),
	 @Answer 	[nvarchar] (4000))

AS INSERT INTO [rb_FAQs]
	([ModuleID],
	 [CreatedByUser],
	 [CreatedDate],
	 [Question],
	 [Answer]) 
 
VALUES 
	 (@ModuleID,
	  @UserName,
	  GetDate(),
	  @Question,
	  @Answer)

SELECT 
	@ItemID = @@Identity

GO


if exists (select * from dbo.sysobjects where id = object_id(N'[rb_DeleteFAQ]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [rb_DeleteFAQ]
GO

CREATE PROCEDURE [rb_DeleteFAQ]
	(@ItemID 	[int])

AS DELETE FROM [rb_FAQs]

WHERE 
	( [ItemID] = @ItemID)

GO


if exists (select * from dbo.sysobjects where id = object_id(N'[rb_GetFAQ]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [rb_GetFAQ]
GO

CREATE PROCEDURE rb_GetFAQ

(@ModuleID int)

AS

SELECT ItemID, CreatedByUser, CreatedDate, Question, Answer
FROM rb_FAQs 
WHERE ModuleID = @ModuleID
ORDER BY Question
GO


if exists (select * from dbo.sysobjects where id = object_id(N'[rb_GetSingleFAQ]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [rb_GetSingleFAQ]
GO

CREATE PROCEDURE rb_GetSingleFAQ 
(@ItemID int)

AS

SELECT CreatedByUser, CreatedDate, Question, Answer
FROM rb_FAQs
WHERE ItemID = @ItemID

GO


if exists (select * from dbo.sysobjects where id = object_id(N'[rb_UpdateFAQ]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [rb_UpdateFAQ]
GO

CREATE PROCEDURE [rb_UpdateFAQ]
	(@ItemID 	[int],
	 @UserName	[nvarchar] (100),
	 @Question 	[nvarchar] (500),
	 @Answer 	[nvarchar] (4000))

AS UPDATE [rb_FAQs]

SET  
	 [CreatedByUser] = @UserName,
	 [Question]	 = @Question,
	 [Answer]	 = @Answer 

WHERE 
	( [ItemID]	 = @ItemID)

GO