常用到的存储过程

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