常用到的存储过程

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[LogComm_insert]
@log_id int,
@log_comm_ip nchar(15),
@log_comm_name nvarchar(10),
@log_comm_content ntext
AS
BEGIN
SET NOCOUNT ON;

INSERT INTO [LogComm] ([log_id], [log_comm_ip], [log_comm_name], [log_comm_content], [log_comm_date]) VALUES (@log_id, @log_comm_ip, @log_comm_name, @log_comm_content, GETDATE())

SELECT [log_id], [log_comm_ip], [log_comm_id], [log_comm_name], [log_comm_content], [log_comm_date] FROM [LogComm] WHERE ([log_comm_id] = SCOPE_IDENTITY())

UPDATE [Log] SET [log_comm_num] = [log_comm_num] + 1 WHERE [log_id] = @log_id

END

////搜索存储过程使用

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[search]
@flag tinyint,
@str nvarchar(100),
@page_size int,
@current_index int
AS
BEGIN
SET NOCOUNT ON;

--log结果集数目
IF @flag = '1'
SELECT COUNT(*) FROM [Log] WHERE [log_heading] Like '%' + @str + '%' OR [log_subhead] Like '%' + @str + '%' OR [log_content] Like '%' + @str + '%'

--log结果集
ELSE IF @flag = '2'
  BEGIN
   WITH [LogTab] AS
   (
    SELECT ROW_NUMBER() OVER(ORDER BY [ontop] DESC, [log_date] DESC) AS [LogID], * FROM [Log] WHERE [draft] = 0 AND [log_heading] Like '%' + @str + '%' OR [log_subhead] Like '%' + @str + '%' OR [log_content] Like '%' + @str + '%'
   )
   SELECT * FROM [LogTab] WHERE [LogID] BETWEEN ((@current_index - 1) * @page_size + 1) AND @page_size * @current_index
  END
  --SELECT * FROM [Log] WHERE [log_heading] Like '%' + @str + '%' OR [log_subhead] Like '%' + @str + '%' OR [log_content] Like '%' + @str + '%' ORDER BY [ontop] DESC, [log_date] DESC

--logcomm结果集数目
ELSE IF @flag = '3'
SELECT COUNT(*) FROM [Log] WHERE [log_id] IN (SELECT [log_id] FROM [LogComm] WHERE [log_comm_content] Like '%' + @str + '%' OR [log_comm_name] Like '%' + @str + '%')

--logcomm结果集
ELSE IF @flag = '4'
  BEGIN
   WITH [LogCommTab] AS
   (
    SELECT ROW_NUMBER() OVER(ORDER BY [ontop] DESC, [log_date] DESC) AS [LogID], * FROM [Log] WHERE [draft] = 0 AND [log_id] IN (SELECT [log_id] FROM [LogComm] WHERE [log_comm_content] Like '%' + @str + '%' OR [log_comm_name] Like '%' + @str + '%')
   )
   SELECT * FROM [LogCommTab] WHERE [LogID] BETWEEN ((@current_index - 1) * @page_size + 1) AND @page_size * @current_index
  END
  --SELECT * FROM [Log] WHERE [log_id] IN (SELECT [log_id] FROM [LogComm] WHERE [log_comm_content] Like '%' + @str + '%' OR [log_comm_name] Like '%' + @str + '%') ORDER BY [ontop] DESC, [log_date] DESC

--msg结果集数目
ELSE IF @flag = '5'
SELECT COUNT(*) FROM [Msg] WHERE [msg_heading] Like '%' + @str + '%' OR [msg_content] Like '%' + @str + '%' OR [msg_name] Like '%' + @str + '%'

--msg结果集
ELSE IF @flag = '6'
  BEGIN
   WITH [MsgTab] AS
   (
    SELECT ROW_NUMBER() OVER(ORDER BY [msg_date] DESC) AS [MsgID], * FROM [Msg] WHERE [msg_heading] Like '%' + @str + '%' OR [msg_content] Like '%' + @str + '%' OR [msg_name] Like '%' + @str + '%'
   )
   SELECT * FROM [MsgTab] WHERE [MsgID] BETWEEN ((@current_index - 1) * @page_size + 1) AND @page_size * @current_index
  END
  --SELECT * FROM [Msg] WHERE [msg_heading] Like '%' + @str + '%' OR [msg_content] Like '%' + @str + '%' OR [msg_name] Like '%' + @str + '%' ORDER BY [msg_date] DESC

--pic结果集数目
ELSE IF @flag = '7'
  SELECT COUNT(*) FROM [Pic] WHERE [pic_info] Like '%' + @str + '%'

--pic结果集
ELSE IF @flag = '8'
  BEGIN
   WITH [PicTab] AS
   (
    SELECT ROW_NUMBER() OVER(ORDER BY [pic_date] DESC) AS [PicID], * FROM [Pic] WHERE [pic_info] Like '%' + @str + '%'
   )
   SELECT * FROM [PicTab] WHERE [PicID] BETWEEN ((@current_index - 1) * @page_size + 1) AND @page_size * @current_index
  END
  --SELECT * FROM [Pic] WHERE [pic_info] Like '%' + @str + '%' ORDER BY [pic_date] DESC

--piccomm结果集数目
ELSE IF @flag = '9'
SELECT COUNT(*) FROM [Pic] WHERE [pic_id] IN (SELECT [pic_id] FROM [PicComm] WHERE [pic_comm_content] Like '%' + @str + '%' OR [pic_comm_name] Like '%' + @str + '%')

--piccomm结果集
ELSE IF @flag = '10'
  BEGIN
   WITH [PicCommTab] AS
   (
    SELECT ROW_NUMBER() OVER(ORDER BY [pic_date] DESC) AS [PicID], * FROM [Pic] WHERE [pic_id] IN (SELECT [pic_id] FROM [PicComm] WHERE [pic_comm_content] Like '%' + @str + '%' OR [pic_comm_name] Like '%' + @str + '%')
   )
   SELECT * FROM [PicCommTab] WHERE [PicID] BETWEEN ((@current_index - 1) * @page_size + 1) AND @page_size * @current_index
  END
  --SELECT * FROM [Pic] WHERE [pic_id] IN (SELECT [pic_id] FROM [PicComm] WHERE [pic_comm_content] Like '%' + @str + '%' OR [pic_comm_name] Like '%' + @str + '%') ORDER BY [pic_date] DESC
END