首页 > 代码库 > 数据库存储过程实现增删改差(SQL SERVER 2008)

数据库存储过程实现增删改差(SQL SERVER 2008)

新建数据表:

CREATE TABLE [dbo].[szsj_message_list](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [name] [nvarchar](32) NULL,
    [message] [nvarchar](256) NOT NULL,
    [create_by] [nvarchar](32) NOT NULL,
    [create_date] [datetime] NOT NULL,
    [update_by] [nvarchar](32) NULL,
    [update_date] [datetime] NULL
) ON [PRIMARY]

实现:

CREATE PROCEDURE [dbo].[Op_Announce_Info]
 
 @I_flag nvarchar(32),
 @id nvarchar(32),
 @title nvarchar(128),
 @content nvarchar(2048),
 @files nvarchar(32),
 @create_by nvarchar(32),
 @create_date nvarchar(32)
 
AS
 
BEGIN  
 if @I_flag = insert
   begin
INSERT INTO szsj_announce_info
           (title
           ,content
           ,files
           ,create_by
           ,create_date)
     VALUES
           (@title,
 @content,
 @files,
 @create_by,
 GETDATE());
   end
   
 if @I_flag = update
   begin
     update szsj_announce_info 
        set title = @title,
            content = @content,
            files = @files,
            update_by = @create_by,
            update_date = GETDATE()
      where id = @id;
   end
   
 if @I_flag = updatetimes
   begin
     update szsj_announce_info 
        set times = isnull(times,0) + 1
      where id = @id;
   end
   
 if @I_flag = del
   begin
     delete szsj_announce_info where id = @id;
   end
   
 if @I_flag = show
   begin
     select * from szsj_announce_info where create_date between GETDATE()-15 and GETDATE();
   end
   
 if @I_flag = select
  declare @sql NVARCHAR(MAX);
  set @sql = select * from szsj_announce_info where 1=1 
  begin
   if LEN (@id) > 0
  begin
    set @sql = @sql +  and id = ‘‘‘ + @id + ‘‘‘‘ ;
  end
   
   if LEN (@create_date) > 0
  begin
    set @sql = @sql +  and CONVERT(date,create_date,120) = convert(date,‘‘‘ + @create_date + ‘‘‘,120) ;
  end
  
   if LEN (@title) > 0
  begin
    set @sql = @sql +  and title like ‘‘% + @title + %‘‘‘ ;
  end
  EXEC sp_executesql @sql;
  end
 
END

 

数据库存储过程实现增删改差(SQL SERVER 2008)