資料庫預存程序實現增刪改差（SQL SERVER 2008）

標籤：bsp   var   content   prim   pre   values   sage   arc   datetime   

建立資料表：

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‘   beginINSERT 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）