02.02.02.sqldataprovider

完整的商业模板和强大的后台管理功能

if exists (select * from {databaseOwner}sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}AddSearchWord') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure {databaseOwner}{objectQualifier}AddSearchWord
GO

/****** Object:  Stored Procedure {databaseOwner}{objectQualifier}UpdateSearchWord    Script Date: Sunday, September 26, 2004 ******/
if exists (select * from {databaseOwner}sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}UpdateSearchWord') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure {databaseOwner}{objectQualifier}UpdateSearchWord
GO

/****** Object:  Stored Procedure {databaseOwner}{objectQualifier}DeleteSearchWord    Script Date: Sunday, September 26, 2004 ******/
if exists (select * from {databaseOwner}sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DeleteSearchWord') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure {databaseOwner}{objectQualifier}DeleteSearchWord
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}GetSearchItem
	@ModuleId int
	,@SearchKey varchar(100) 
AS

SELECT
	[SearchItemID],
	[Title],
	[Link],
	[Description],
	[Author],
	[PubDate],
	[ModuleId],
	[SearchKey],
	[Content],
	[Guid],
	[HitCount]
FROM
	{databaseOwner}{objectQualifier}SearchItem
WHERE
	[ModuleID] = @ModuleID AND
	[SearchKey] = @SearchKey
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}ListSearchItem
AS

SELECT
	[SearchItemID],
	[Title],
	[Link],
	[Description],
	[Author],
	[PubDate],
	[ModuleId],
	[SearchKey],
	[Content],
	[Guid],
	[HitCount]
FROM
	{databaseOwner}{objectQualifier}SearchItem
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}AddSearchItem
	@Title nvarchar(200),
	@Link nvarchar(256),
	@Description nvarchar(2000),
	@Author int,
	@ModuleId int,
	@SearchKey nvarchar(100),
	@Content text
AS

INSERT INTO {databaseOwner}{objectQualifier}SearchItem (
	[Title],
	[Link],
	[Description],
	[Author],
	[PubDate],
	[ModuleId],
	[SearchKey],
	[Content],
	[Guid],
	[HitCount]
) VALUES (
	@Title,
	@Link,
	@Description,
	@Author,
	GETDATE(),
	@ModuleId,
	@SearchKey,
	@Content,
	CONVERT(nvarchar(40), NEWID()),
	0
)

select SCOPE_IDENTITY()
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}UpdateSearchItem
	@SearchItemID int, 
	@Title varchar(200), 
	@Link varchar(256), 
	@Description varchar(500), 
	@Author int, 
	@PubDate datetime, 
	@ModuleId int, 
	@SearchKey varchar(100), 
	@Content text, 
	@Guid nvarchar(40), 
	@HitCount int 
AS

UPDATE {databaseOwner}{objectQualifier}SearchItem SET
	[Title] = @Title,
	[Link] = @Link,
	[Description] = @Description,
	[Author] = @Author,
	[PubDate] = @PubDate,
	[ModuleId] = @ModuleId,
	[SearchKey] = @SearchKey,
	[Content] = @Content,
	[Guid] = @Guid,
	[HitCount] = @HitCount
WHERE
	[SearchItemID] = @SearchItemID
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DeleteSearchItem
	@SearchItemID int
AS

DELETE FROM {databaseOwner}{objectQualifier}SearchItem
WHERE
	[SearchItemID] = @SearchItemID
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}GetSearchItemWord
	@SearchItemWordID int
	
AS

SELECT
	[SearchItemWordID],
	[SearchItemID],
	[SearchWordsID],
	[Occurrences]
FROM
	{databaseOwner}{objectQualifier}SearchItemWord
WHERE
	[SearchItemWordID] = @SearchItemWordID
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}ListSearchItemWord
AS

SELECT
	[SearchItemWordID],
	[SearchItemID],
	[SearchWordsID],
	[Occurrences]
FROM
	{databaseOwner}{objectQualifier}SearchItemWord
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}GetSearchItemWordBySearchItem
	@SearchItemID int
AS

SELECT
	[SearchItemWordID],
	[SearchItemID],
	[SearchWordsID],
	[Occurrences]
FROM
	{databaseOwner}{objectQualifier}SearchItemWord
WHERE
	[SearchItemID]=@SearchItemID
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}GetSearchItemWordBySearchWord
	@SearchWordsID int
AS

SELECT
	[SearchItemWordID],
	[SearchItemID],
	[SearchWordsID],
	[Occurrences]
FROM
	{databaseOwner}{objectQualifier}SearchItemWord
WHERE
	[SearchWordsID]=@SearchWordsID
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}AddSearchItemWord
	@SearchItemID int,
	@SearchWordsID int,
	@Occurrences int
AS

INSERT INTO {databaseOwner}{objectQualifier}SearchItemWord (
	[SearchItemID],
	[SearchWordsID],
	[Occurrences]
) VALUES (
	@SearchItemID,
	@SearchWordsID,
	@Occurrences
)

select SCOPE_IDENTITY()
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}UpdateSearchItemWord
	@SearchItemWordID int, 
	@SearchItemID int, 
	@SearchWordsID int, 
	@Occurrences int 
AS

UPDATE {databaseOwner}{objectQualifier}SearchItemWord SET
	[SearchItemID] = @SearchItemID,
	[SearchWordsID] = @SearchWordsID,
	[Occurrences] = @Occurrences
WHERE
	[SearchItemWordID] = @SearchItemWordID
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DeleteSearchItemWord
	@SearchItemWordID int
AS

DELETE FROM {databaseOwner}{objectQualifier}SearchItemWord
WHERE
	[SearchItemWordID] = @SearchItemWordID
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}GetSearchItemWordPosition
	@SearchItemWordPositionID int
	
AS

SELECT
	[SearchItemWordPositionID],
	[SearchItemWordID],
	[ContentPosition]
FROM
	{databaseOwner}{objectQualifier}SearchItemWordPosition
WHERE
	[SearchItemWordPositionID] = @SearchItemWordPositionID
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}ListSearchItemWordPosition
AS

SELECT
	[SearchItemWordPositionID],
	[SearchItemWordID],
	[ContentPosition]
FROM
	{databaseOwner}{objectQualifier}SearchItemWordPosition
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}GetSearchItemWordPositionBySearchItemWord
	@SearchItemWordID int
AS

SELECT
	[SearchItemWordPositionID],
	[SearchItemWordID],
	[ContentPosition]
FROM
	{databaseOwner}{objectQualifier}SearchItemWordPosition
WHERE
	[SearchItemWordID]=@SearchItemWordID
GO

CREATE  PROCEDURE {databaseOwner}{objectQualifier}AddSearchItemWordPosition
	@SearchItemWordID int,
	@ContentPositions varChar(2000)
AS

	SET NOCOUNT ON

	DECLARE @TempList table
	(
		ItemWordID int,
		Position int
	)

	DECLARE @Position varchar(10), @Pos int

	SET @ContentPositions = LTRIM(RTRIM(@ContentPositions))+ ','
	SET @Pos = CHARINDEX(',', @ContentPositions, 1)

	IF REPLACE(@ContentPositions, ',', '') <> ''
	BEGIN
		WHILE @Pos > 0
		BEGIN
			SET @Position = LTRIM(RTRIM(LEFT(@ContentPositions, @Pos - 1)))
			IF @Position <> ''
			BEGIN
				INSERT INTO @TempList (ItemWordID, Position) VALUES (@SearchItemWordID, CAST(@Position AS int)) 
			END
			SET @ContentPositions = RIGHT(@ContentPositions, LEN(@ContentPositions) - @Pos)
			SET @Pos = CHARINDEX(',', @ContentPositions, 1)

		END
	END	

	INSERT INTO {databaseOwner}{objectQualifier}SearchItemWordPosition (
		[SearchItemWordID],
		[ContentPosition]) 
	SELECT ItemWordID, Position FROM @TempList


GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}UpdateSearchItemWordPosition
	@SearchItemWordPositionID int, 
	@SearchItemWordID int, 
	@ContentPosition int 
AS

UPDATE {databaseOwner}{objectQualifier}SearchItemWordPosition SET
	[SearchItemWordID] = @SearchItemWordID,
	[ContentPosition] = @ContentPosition
WHERE
	[SearchItemWordPositionID] = @SearchItemWordPositionID
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DeleteSearchItemWordPosition
	@SearchItemWordPositionID int
AS

DELETE FROM {databaseOwner}{objectQualifier}SearchItemWordPosition
WHERE
	[SearchItemWordPositionID] = @SearchItemWordPositionID
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}GetSearchWordByID
	@SearchWordsID int
	
AS

SELECT
	[SearchWordsID],
	[Word],
	[IsCommon],
	[HitCount]
FROM
	{databaseOwner}{objectQualifier}SearchWord
WHERE
	[SearchWordsID] = @SearchWordsID
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}GetSearchWords
AS

SELECT
	[SearchWordsID],
	[Word],
	[HitCount]
FROM
	{databaseOwner}{objectQualifier}SearchWord
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}AddSearchWord
	@Word nvarchar(100)
AS

INSERT INTO {databaseOwner}{objectQualifier}SearchWord (
	[Word],
	[IsCommon],
	[HitCount]
) VALUES (
	@Word,
	0,
	1
)

select SCOPE_IDENTITY()
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}UpdateSearchWord
	@SearchWordsID int, 
	@Word nvarchar(100), 
	@IsCommon bit, 
	@HitCount int 
AS

UPDATE {databaseOwner}{objectQualifier}SearchWord SET
	[Word] = @Word,