Mã hoá trong SQL Server 2005

Mã hóa dữ liệu bằng MsSql
Phần 1: Mã hóa
Phần 2: Giải mã

Mã hoá là một phương pháp quan trọng nhằm bảo mật dữ liệu. Những dữ liệu nhạy cảm như số CMT, số thẻ tín dụng, mật khẩu… cần phải được bảo vệ trước vô vàn mối nguy hiểm tấn công hiện nay. Trong SQL Server 2000 bạn có thể tự tạo các hàm của riêng mình hoặc sử dụng các DLL ngoài để mã hoá dữ liệu. Trong SQL Server 2005, các hàm và phương thức này được mặc định cho phép sẵn.

SQL Server 2005 cung cấp các kỹ thuật sau để mã hoá dữ liệu

      Mã hoá bằng mật khẩu
      Mã hoá khoá đối xứng
      Mã hoá khoá không đối xứng
      Mã hoá chứng nhận

Trong phần đầu của loạt bài này, chúng tôi sẽ giải thích cách sử dụng kỹ thuật mã hoá bằng mật khẩu và phương pháp giải mã nó.

SQL Server 2005 cung cấp 2 hàm cho việc mã hoá: một cho việc mã hoá và một cho việc giải mã.

Mã hoá bằng mật khẩu là phương pháp mã hoá dữ liệu cơ bản thông qua mật khẩu. Dữ liệu có thể được giải mã nếu nhập đúng mật khẩu đã sử dụng khi mã hoá. Chúng ta sẽ thử một ví dụ về việc mã hoá và giải mã dữ liệu bằng kỹ thuật mã hoá thông qua mật khẩu.

select EncryptedData = EncryptByPassPhrase('MAK', '123456789' )

Kết quả

0x0100000000214F5A73054F3AB954DD23571154019F3EFC031ABFCCD258FD22ED69A48002

Giờ chúng ta sẽ thực thi 3 lần hàm Encryptbypassphrase trên theo ví dụ sau

declare @count int
declare @SocialSecurityNumber varchar(500)
declare @password varchar(12)
set @count =1
while @count<=3
begin
set @SocialSecurityNumber = '123456789'
set @Password = 'MAK'
select EncryptedData = EncryptByPassPhrase(@password, @SocialSecurityNumber )
set @count=@count+1
end

Kết quả

0x01000000CBB7EE45B5C1460D6996B149CE16B76C7F7CD598DC56364D106B05D47B930093

0x010000005E884D30C8FF7E4723D4E70A03B0B07F877667BAF1DA9BE1E116434842D11B99

0x01000000C508FB0C4FC7734B47B414D2602A71A338417DD685229173684D319334A084CD

Lưu ý:
“123456789” ở đây có thể là số thẻ tín dụng và “MAK” là mật khẩu

Kết quả của Encryptbypassphrase sau mỗi lần thực thi hàm là khác nhau. Tuy nhiên, khi bạn giải mã dữ liệu thì nó vẫn ra kết quả như ban đầu trước khi mã hoá.

Giờ chúng ta sẽ thử giải mã dữ liệu đã được mã hoá ở trên với hàm DecryptByPassPhrase

select convert(varchar(100),DecryptByPassPhrase('MAK', 0x01000000CBB7EE45B5C1460D6996B149CE16B76C7F7CD598DC56364D106B05D47B930093))

select convert(varchar(100),DecryptByPassPhrase('MAK', 0x010000005E884D30C8FF7E4723D4E70A03B0B07F877667BAF1DA9BE1E116434842D11B99))

select convert(varchar(100),DecryptByPassPhrase('MAK', 0x01000000C508FB0C4FC7734B47B414D2602A71A338417DD685229173684D319334A084CD))

Kết quả

123456789

123456789

123456789

Thử giải mã dữ liệu đã được mã hoá với một mật khẩu khác. Thực thi theo câu lệnh sau

select convert(varchar(100),DecryptByPassPhrase('test', 0x01000000C508FB0C4FC7734B47B414D2602A71A338417DD685229173684D319334A084CD))

Kết quả

NULL

Kết quả cho bạn thấy SQL Server trả lại giá trị NULL nếu mật khẩu sai.
Giờ chúng ta sẽ thử tạo một bảng chứa số thẻ tín dụng và số CMT, sau đó sẽ mã hoá dữ liệu này thông qua phương pháp mã hoá mật khẩu.

USE [master]
GO
/****** Object: Database [admin] Script Date: 11/25/2007 10:50:47 ******/
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'Customer DB')
DROP DATABASE [Customer DB]
go

create database [Customer DB]
go
use [Customer DB]
go

create table [Customer data]
([customer id] int,
[Credit Card Number] bigint,
[Social Security Number] bigint)
go

insert into [Customer data] values (1, 1234567812345678, 123451234)
insert into [Customer data] values (2, 1234567812345378, 323451234)
insert into [Customer data] values (3, 1234567812335678, 133451234)
insert into [Customer data] values (4, 1234567813345678, 123351234)
insert into [Customer data] values (5, 1234563812345678, 123431234)
go

Tạo hai cột để lưu dữ liệu đã được mã hoá

use [Customer DB]

go
alter table [Customer Data] add
[Encrypted Credit Card Number] varbinary(MAX)
go
alter table [Customer Data] add
[Encrypted Social Security Number] varbinary(MAX)
go

Cập nhật dữ liệu đã được mã hoá vào hai cột vừa tạo
use [Customer DB]
go
update [Customer Data] set [Encrypted Credit Card Number] =
EncryptByPassPhrase('Credit Card', convert(varchar(100),[Credit Card Number]) )
go
update [Customer Data] set [Encrypted Social Security Number] =
EncryptByPassPhrase('Social Security', convert(varchar(100),[Social Security Number]) )
Go

Truy vẫn bảng bằng các lệnh sau (hình 1)

use [Customer DB]
go
select * from [customer data]
go

Kết quả

Xoá bỏ cột chứa dữ liệu chưa được mã hoá

use [Customer DB]
go
alter table [Customer Data] drop column [Credit Card Number]
go
alter table [Customer Data] drop column [Social Security Number]
go

Truy vấn bảng theo các lệnh sau (hình 2)

use [Customer DB]
go
select * from [customer data]
go

Kết quả

Giải mã dữ liệu trên bảng thông qua hàm Decryptbypassphrase như sau (hình 3)

use [Customer DB]
go
select
[customer id],
convert(bigint,convert(varchar(100),decryptbypassphrase('Credit Card',[Encrypted Credit Card Number]) )) as
[Credit Card Number],
convert(bigint,convert(varchar(100),decryptbypassphrase('Social Security',[Encrypted Social Security Number] ) )) as
[Social Security Number] from [customer data]
Go

 

Kết luận
Mã hoá dữ liệu thực sự rất quan trọng. Thông qua bài này chúng tôi đã giới thiệu đến các bạn một trong 4 kỹ thuật mã hoá sẵn có trong SQL Server 2005 – kỹ thuật mã hoá bằng mật khẩu – và phương pháp giải mã nó.

Xây dựng cơ sở dữ liệu cho một website phần 8

Loại: Viết store procedure phần 5

Các bài cần theo dõi của bài viết: Xây dựng cơ sở dữ liệu cho một website

Phần 1: Tạo database

Phần 2: Viết Trigger

Phần 3: Viết store procedure truy vấn, thêm, sửa, xóa một chuyên mục

Phần 4: Viết store procedure hiển thị menu đa cấp

Phần 5: Viết store Procedure thêm, sửa, xóa một bài viết

Phần 6: Viết store procedure hiển thị danh sách bài viết

Phần 7: Viết store procedure tìm kiếm bài viết

Phần 8: Viết store procedure top 10 các bài viết mới và cũ

Bài này mình sẽ hướng dẫn truy vấn danh sách các bài viết khác bao gôm 10 bài viết mới hơn và 10 bài viết cũ hơn gần nhất (Nếu có), cùng chuyên mục của bài viết được chọn.

Thường khi làm web khi người dùng chọn đọc một bài viết, bạn cần liệt kê cho người dùng danh sách các bài viết khác cùng chuyên mục. Thường đề làm điều này bạn sẽ dùng code lập trình hoặc sẽ phải viết nhiều store hoặc dùng vòng lặp đề thực hiện. Nhưng ở đây mình sẽ viết để thực hiện trong cơ sở dữ liệu.
Để làm được trước tiên bạn cần viết 2 hàm.
- Hàm trả về 10 bài viết mới hơn cùng chuyên mục, gần nhất với bài viết được chọn

-- =============================================
-- Author:        webmaster@hmweb.com.vn

-- Description:   <Hàm trà về 10 bài viết mới hơn, cùng chuyên mục, gần nhất với bài viết được chọn>

-- =============================================

CREATE FUNCTION [dbo].[fuNewOfNews]

(

      @idNews int,

      @CateID int

)RETURNS @TB_News_New TABLE

(    

      idNews int,

      Title nvarchar(2000)

)

AS 

BEGIN

      INSERT INTO @TB_News_New     

      SELECT TOP 10  TB_News.idNews,

      '<A href='+ dbo.ufDomainName()+TB_News.Link +' >'+TB_News.Title+'</a>' as Title    

      FROM  TB_News INNER JOIN

      TB_Category ON TB_News.CateID = TB_Category.CateID

      WHERE TB_News.idNews>@idNews AND TB_News.CateID=@CateID AND TB_News.Status=1             

      ORDER BY TB_News.idNews DESC 

      RETURN 

END

- Hàm trả về 10 bài viết cũ hơn cùng chuyên mục, gần nhất với bài viết được chọn

-- =============================================

-- Author:        webmaster@hmweb.com.vn

-- Description:   <Hàm trà về 10 bài viết cũ hơn, cùng chuyên mục, gần nhất với bài viết được chọn>

-- =============================================



CREATE FUNCTION [dbo].[fuOldOfNews]

(

      @idNews int,

      @CateID int

)

RETURNS @News_Old TABLE

(    

      idNews int,

      Title nvarchar(2000)

)

AS 

BEGIN

      INSERT INTO @News_Old  

      SELECT TOP 10  TB_News.idNews,

      '<A href='+ dbo.ufDomainName()+TB_News.Link +' >'+TB_News.Title+'</a>' as Title

      FROM  TB_News INNER JOIN

      TB_Category ON TB_News.CateID = TB_Category.CateID

      WHERE TB_News.idNews<@idNews

      AND TB_News.CateID=@CateID   AND TB_News.Status=1                

      ORDER BY TB_News.idNews DESC 

      RETURN 

END

 

Tiếp theo mình sẽ viết Store procedure đề lấy danh sách 10 bài viết mới hơn và 10 bài viết cũ hơn gần nhất (Nếu có)

 

-- =============================================

-- Author:        webmaster@hmweb.com.vn

-- Description:   <truy vấn danh sách các bài viết khác cùng chuyên mục>

-- =============================================

 CREATE PROCEDURE [dbo].[spTB_News_SelectCacBaiVietKhac]

 @idNews int,

 @CateID int

 AS

 BEGIN     

      DECLARE @SQL nvarchar(4000)  

      SET @SQL=''

      -- Kiểm tra có bài viết mới hơn không

      DECLARE @countNew int  

      SELECT  @countNew = count(idNews) FROM TB_News

      WHERE TB_News.idNews>@idNews AND CateID=@CateID AND  Status=1    

      IF @countNew IS NULL  SET @countNew=0    

      -- Kiểm tra có bài viết cũ hơn không

      DECLARE @countOld int

      SELECT @countOld = COUNT(idNews) FROM TB_News

      WHERE idNews<@idNews AND  CateID=@CateID AND Status=1      

      IF @countOld IS NULL SET @countOld=0

      --Nếu có bài viết mới hơn

      IF @countNew>0

      BEGIN

            -- Nếu có bài viết cũ hơn thì truy vấn 10 bài mới và 10 bài viết gần nhất

            IF @countOld >0

            BEGIN

                  SET @SQL=@SQL+ N'SELECT 999999999999 AS idNews,

                  N''<b>[Các bài viết mới hơn]</b>'' AS Title 

                  Union

                  SELECT TOP 10  TB_News.idNews,

                  ''<A href=''+ dbo.ufDomainName()+TB_News.Link +''>''+TB_News.Title+''</a>'' as Title FROM TB_News

                  INNER JOIN TB_Category ON TB_News.CateID = TB_Category.CateID                           

                        WHERE  TB_News.Status=1 AND TB_News.idNews>'+cast(@idNews AS nvarchar(20))+' AND

                        TB_News.CateID='+cast(@CateID AS nvarchar(20))+'     

                   '

                   SET @SQL =@SQL+'

                   Union SELECT '+cast(@idNews AS nvarchar(20))+' AS idNews,

                        N''<b>[Các bài đã đăng]</b>'' AS Title

                        Union

                        Select idNews, Title from [dbo].[fuOldOfNews]('+Cast(@idNews AS nvarchar(20))+','+Cast(@CateID AS nvarchar(20))+')               

                        ORDER BY TB_News.idNews DESC'

            END

            -- Ngược lại chỉ truy vấn 10 bài viết mới hơn gần nhất

            ELSE

                  BEGIN

                        SET @SQL =@SQL+N'SELECT 999999999999 AS idNews, N''<b>[Các bài mới hơn]</b>'' AS Title

                         UNION           

                         Select idNews, Title from [dbo].[fuNewOfNews]('+Cast(@idNews AS nvarchar(20))+','+Cast(@CateID AS nvarchar(20))+')               

                         ORDER BY idNews DESC' 

                  END

      END

      -- Nếu không có bài viết mới hơn => truy vấn 10 bài viết cũ hơn gần nhất

      ELSE

            BEGIN

                  SET @SQL =@SQL+N'SELECT 999999999999 AS idNews, N''<b>[Các bài đã đăng]</b>'' AS Title

             UNION                 

             Select idNews, Title from [dbo].[fuOldOfNews]('+Cast(@idNews AS nvarchar(20))+','+Cast(@CateID AS nvarchar(20))+')               

             ORDER BY idNews DESC'       

            END

      --PRINT @SQL

      -- Thực thi trà về danh sách bài viết

             EXEC (@SQL)

END

Ví dụ khi bạn đọc bài viết thứ 13 có id=17 và CateID=5  EXEC [spTB_News_SelectCacBaiVietKhac] 17,5
Kết quả hiển thị như minh họa sau

Xây dựng cơ sở dữ liệu cho một website phần 7

Loại: Viết store procedure phần 5

Các bài cần theo dõi của bài viết: Xây dựng cơ sở dữ liệu cho một website

Phần 1: Tạo database

Phần 2: Viết Trigger

Phần 3: Viết store procedure truy vấn, thêm, sửa, xóa một chuyên mục

Phần 4: Viết store procedure hiển thị menu đa cấp

Phần 5: Viết store Procedure thêm, sửa, xóa một bài viết

Phần 6: Viết store procedure hiển thị danh sách bài viết

Phần 7: Viết store procedure tìm kiếm bài viết

Phần 8: Viết store procedure top 10 các bài viết mới và cũ

Tiếp theo mình sẽ viết thủ tục tìm kiếm bài viết theo điều kiện
Trong thủ tục này mình viết tìm kiếm bài viết đơn giản theo trạng thái (Status), Theo Chuyên mục (CateID) và theo từ khóa (Keyword). Ngoài ra bạn muốn tìm theo các trường khác như Thời gian viết, Thời gian đăng, ... bạn có thể bổ xung vào thủ tục
+ Trang Thái
   - Nếu @Status=1 => Các bài đã đăng
   - Nếu @Status=-1 => Các bài chờ đăng
   - Nếu @Status=0 => Không Set
+ Chuyên mục
   - Nếu @CateID>0 => Tìm theo điều kiện CateID
+ Từ khóa
    Ở đây mình tìm kiếm theo điều kiện LIKE ở trường Title và trường Description (Nếu bạn cần tìm theo các trường khác thì bạn có thể thay đồi)
Thủ tục tìm kiếm minh họa như sau

-- =============================================

-- Author:        webmaster@hmweb.com.vn

-- Description:   <Tìm kiếm bài viết theo điều kiện>

-- =============================================

CREATE PROCEDURE spTB_News_Search

@Status int,

@CateID int,

@Keyword nvarchar(255)

AS

BEGIN

      -- Khai báo chuỗi truy vấn

      DECLARE @SQL nvarchar(4000)

      SET @SQL =''

      SET @SQL =@SQL+ N'

            SELECT    

            TB_Category.CateID,

            TB_Category.CateName,

            TB_News.idNews,

            TB_News.UserName,

            TB_News.Title,

            TB_News.Description,

            TB_News.Content,

            TB_News.Author,

            TB_News.Picture,

            CONVERT(nvarchar(30),TB_News.CreateDate,103) AS CreateDate,

            CONVERT(nvarchar(30),TB_News.PublishDate,103) AS PublishDate,

            CASE TB_News.Status

                        WHEN 1 THEN N''Đã đăng''

                        WHEN 0 THEN N''Chưa đăng'' END AS Status, 

            TB_News.Numberreads,

            TB_News.Link

            FROM         TB_Category INNER JOIN

            TB_News ON TB_Category.CateID = TB_News.CateID WHERE ''1''=''1'''



            --Khai báo điều kiện tìm kiếm

            DECLARE @SQLWhere nvarchar(4000)

            SET @SQLWhere=''



            -- Nếu @Status=1 thì tìm điều kiện Status=1 (Đã đăng)

            IF @Status=1

                  SET @SQLWhere=@SQLWhere+ N' AND TB_News.Status =1 '

            -- Nếu @Status=-1 thì tìm điều kiện Status=0 (Chờ đăng)    

            IF @Status=-1

                  SET @SQLWhere =@SQLWhere+ ' AND TB_News.Status =0 '

            -- Tìm kiếm theo chuyên mục nếu @CateID>0

            IF @CateID>0

                  SET @SQLWhere =@SQLWhere+ ' AND TB_News.CateID = '+cast(@CateID AS char(20)) +''

            -- Tìm kiếm theo từ khóa

            IF @Keyword<>''

                  SET @SQLWhere =@SQLWhere+ '

                  AND ( TB_News.Title LIKE ''%'+@Keyword+ '%''

                  OR TB_News.Description  LIKE ''%'+@Keyword+ '%'')'



            -- Khai báo Chuỗi thực thi tìm kiếm

            DECLARE @SQLEXEC nvarchar(4000)

            -- Kết hợp điều kiện tìm kiếm vào chuỗi truy vấn

            SET @SQLEXEC=@SQL+@SQLWhere  

            -- Thực thi điều kiện tìm kiếm và trả về dữ liệu nếu có

            EXEC sp_executesql @SQLEXEC

 --PRINT @SQLEXEC

END

 

Xây dựng cơ sở dữ liệu cho một website phần 6

Loại: Viết store procedure phần 4

Các bài cần theo dõi của bài viết: Xây dựng cơ sở dữ liệu cho một website

Phần 1: Tạo database

Phần 2: Viết Trigger

Phần 3: Viết store procedure truy vấn, thêm, sửa, xóa một chuyên mục

Phần 4: Viết store procedure hiển thị menu đa cấp

Phần 5: Viết store Procedure thêm, sửa, xóa một bài viết

Phần 6: Viết store procedure hiển thị danh sách bài viết

Phần 7: Viết store procedure tìm kiếm bài viết

Phần 8: Viết store procedure top 10 các bài viết mới và cũ

Bài này sẽ tiếp tục các hàm và thủ tục cho bảng TB_News

1. Trigger để cập nhật tự động trường Link cho bài viết

Để tự động cập nhật trường liên kết cho bài viết bạn có thể thực hiện như Trigger sau

-- Create by webmaster@hmweb.com.vn

CREATE TRIGGER [dbo].[TB_News_InsertTrigger]

ON [dbo].TB_News 

FOR INSERT AS 

DECLARE   @Link nvarchar(100)

DECLARE @NewID int

DECLARE  @CateID int

SELECT  @CateID = (SELECT  CateID FROM  Inserted)

SELECT  @Link=Link FROM TB_Category  WHERE CateID =@CateID

SELECT @NewID=(Select idNews from Inserted)

SET @Link=@Link+'&NewsID='+Cast(@NewID AS nvarchar(20))

--Cập nhật trường liên kết cho bài viết

UPDATE  TB_News SET  Link=@Link

      WHERE idNews=(Select idNews from Inserted)

2.  Store procedure Truy vấn danh sách bài viết

Bài viết có thể ở 1 trong 2 trạng thái đã đăng hoặc chưa đăng (Ở đây mình phân tích chỉ 2 trạng thái, nếu ứng dụng của bạn có yêu cầu phân quyền nhiều cấp như trạng thái chờ đăng, trạng thái chờ duyệt, trạng thái đăng ... thì bạn sửa lại cho phù hợp). Trong store procedure sau tùy theo @Action đề lấy bài viết theo trạng thái tương ứng:
- Nếu @Action=0 thì truy vấn tất cả - cả đã đăng và chờ đăng
- Nếu @Action=1 thì truy vấn các bài đã đăng
- Nếu @Action=-1 thì truy vẫn các bài chờ đăng.
Khi sử dụng store này tùy vào yêu cầu mà bạn Set giá trị của @Action

-- =============================================

-- Author:        webmaster@hmweb.com.vn

-- Description:   <Truy vấn bài viết>

-- =============================================

CREATE PROCEDURE [dbo].[spRB_News_Select]

@Action int,

@idNews int

AS

BEGIN

      -- Nếu @Action=1 => truy vấn bài viết có trạng thái đã đăng

      IF @Action=1

      BEGIN

            -- Nếu @idNews=0 => truy vấn hết các bài đã đăng

            IF @Action=0

            BEGIN

                  SELECT    

                  TB_Category.CateID,

                  TB_Category.ParentID,

                  TB_Category.CateName,

                  TB_News.idNews,

                  TB_News.UserName,

                  TB_News.Title,

                  TB_News.Description,

                  TB_News.Content,

                  TB_News.Author,

                  TB_News.Picture,

                  -- Chuyển thời gian thành dd/MM/yyyy

                  CONVERT(nvarchar(30),TB_News.CreateDate,103) AS CreateDate,

                  CONVERT(nvarchar(30),TB_News.PublishDate,103) AS PublishDate,

                  CASE TB_News.Status

                        WHEN 1 THEN N'Đã đăng'

                        WHEN 0 THEN N'Chưa đăng' END AS Status, 

                  TB_News.Numberreads,

                  TB_News.Link,

                  TB_Category.Decen,

                  TB_Category.Depth

                  FROM  TB_Category INNER JOIN

                  TB_News ON TB_Category.CateID = TB_News.CateID

                  WHERE TB_News.Status=1

            END

            ELSE

            --Nếu @idNew>0 => Truy vấn bài viết đã đăng theo idNews

            BEGIN

                  -- Cập nhật số lần xem bài viết

                  DECLARE @Numberreads int

                  SET @Numberreads=0

                  SELECT @Numberreads=Numberreads FROM TB_News WHERE idNews=@idNews

                  UPDATE TB_News

                  SET

                        Numberreads =@Numberreads +1

                  WHERE idNews=@idNews

                  -- Nếu cập nhật thành công => Select

                  IF @@ERROR=0     

                  SELECT    

                  TB_Category.CateID,

                  TB_Category.ParentID,

                  TB_Category.CateName,

                  TB_News.idNews,

                  TB_News.UserName,

                  TB_News.Title,

                  TB_News.Description,

                  TB_News.Content,

                  TB_News.Author,

                  TB_News.Picture,

                  -- Chuyển thời gian thành dd/MM/yyyy

                  CONVERT(nvarchar(30),TB_News.CreateDate,103) AS CreateDate,

                  CONVERT(nvarchar(30),TB_News.PublishDate,103) AS PublishDate,

                  CASE TB_News.Status

                        WHEN 1 THEN N'Đã đăng'

                        WHEN 0 THEN N'Chưa đăng' END AS Status, 

                  TB_News.Numberreads,

                  TB_News.Link,

                  TB_Category.Decen,

                  TB_Category.Depth

                  FROM  TB_Category INNER JOIN

                  TB_News ON TB_Category.CateID = TB_News.CateID

                  WHERE TB_News.Status=1 AND TB_News.idNews=@idNews

            END

      END



      -- Nếu @Action=-1 => truy vấn bài viết có trạng thái chưa đăng

      IF @Action=-1

      BEGIN

            -- Nếu @idNews=0 => truy vấn hết các bài chưa đăng

            IF @Action=0

            BEGIN

                  SELECT    

                  TB_Category.CateID,

                  TB_Category.ParentID,

                  TB_Category.CateName,

                  TB_News.idNews,

                  TB_News.UserName,

                  TB_News.Title,

                  TB_News.Description,

                  TB_News.Content,

                  TB_News.Author,

                  TB_News.Picture,

                  -- Chuyển thời gian thành dd/MM/yyyy

                  CONVERT(nvarchar(30),TB_News.CreateDate,103) AS CreateDate,

                  CONVERT(nvarchar(30),TB_News.PublishDate,103) AS PublishDate,

                  CASE TB_News.Status

                        WHEN 1 THEN N'Đã đăng'

                        WHEN 0 THEN N'Chưa đăng' END AS Status, 

                  TB_News.Numberreads,

                  TB_News.Link,

                  TB_Category.Decen,

                  TB_Category.Depth

                  FROM  TB_Category INNER JOIN

                  TB_News ON TB_Category.CateID = TB_News.CateID

                  WHERE TB_News.Status=0

            END

            ELSE

            --Nếu @idNew>0 => Truy vấn bài viết chưa đăng theo idNews

            BEGIN

                  SELECT    

                  TB_Category.CateID,

                  TB_Category.ParentID,

                  TB_Category.CateName,

                  TB_News.idNews,

                  TB_News.UserName,

                  TB_News.Title,

                  TB_News.Description,

                  TB_News.Content,

                  TB_News.Author,

                  TB_News.Picture,

                  -- Chuyển thời gian thành dd/MM/yyyy

                  CONVERT(nvarchar(30),TB_News.CreateDate,103) AS CreateDate,

                  CONVERT(nvarchar(30),TB_News.PublishDate,103) AS PublishDate,

                  CASE TB_News.Status

                        WHEN 1 THEN N'Đã đăng'

                        WHEN 0 THEN N'Chưa đăng' END AS Status, 

                  TB_News.Numberreads,

                  TB_News.Link,

                  TB_Category.Decen,

                  TB_Category.Depth

                  FROM  TB_Category INNER JOIN

                  TB_News ON TB_Category.CateID = TB_News.CateID

                  WHERE TB_News.Status=0 AND TB_News.idNews=@idNews

            END

      END



      -- Nếu @Action=0 => truy vấn All (Cả trạng thái đã đăng và chưa đăng)

      IF @Action=0

      BEGIN

            -- Nếu @idNews=0 => truy vấn hết các bài chưa đăng

            IF @Action=0

            BEGIN

                  SELECT    

                  TB_Category.CateID,

                  TB_Category.ParentID,

                  TB_Category.CateName,

                  TB_News.idNews,

                  TB_News.UserName,

                  TB_News.Title,

                  TB_News.Description,

                  TB_News.Content,

                  TB_News.Author,

                  TB_News.Picture,

                  -- Chuyển thời gian thành dd/MM/yyyy

                  CONVERT(nvarchar(30),TB_News.CreateDate,103) AS CreateDate,

                  CONVERT(nvarchar(30),TB_News.PublishDate,103) AS PublishDate,

                  CASE TB_News.Status

                        WHEN 1 THEN N'Đã đăng'

                        WHEN 0 THEN N'Chưa đăng' END AS Status, 

                  TB_News.Numberreads,

                  TB_News.Link,

                  TB_Category.Decen,

                  TB_Category.Depth

                  FROM  TB_Category INNER JOIN

                  TB_News ON TB_Category.CateID = TB_News.CateID



            END

            ELSE

            --Nếu @idNew>0 => Truy vấn bài viết chưa đăng theo idNews

            BEGIN

                  SELECT    

                  TB_Category.CateID,

                  TB_Category.ParentID,

                  TB_Category.CateName,

                  TB_News.idNews,

                  TB_News.UserName,

                  TB_News.Title,

                  TB_News.Description,

                  TB_News.Content,

                  TB_News.Author,

                  TB_News.Picture,

                  -- Chuyển thời gian thành dd/MM/yyyy

                  CONVERT(nvarchar(30),TB_News.CreateDate,103) AS CreateDate,

                  CONVERT(nvarchar(30),TB_News.PublishDate,103) AS PublishDate,

                  CASE TB_News.Status

                        WHEN 1 THEN N'Đã đăng'

                        WHEN 0 THEN N'Chưa đăng' END AS Status, 

                  TB_News.Numberreads,

                  TB_News.Link,

                  TB_Category.Decen,

                  TB_Category.Depth

                  FROM  TB_Category INNER JOIN

                  TB_News ON TB_Category.CateID = TB_News.CateID

                  WHERE  TB_News.idNews=@idNews

            END

      END

END

 

Xây dựng cơ sở dữ liệu cho một website phần 5

Loại: Viết store procedure phần 3 

Các bài cần theo dõi của bài viết: Xây dựng cơ sở dữ liệu cho một website

Phần 1: Tạo database

Phần 2: Viết Trigger

Phần 3: Viết store procedure truy vấn, thêm, sửa, xóa một chuyên mục

Phần 4: Viết store procedure hiển thị menu đa cấp

Phần 5: Viết store Procedure thêm, sửa, xóa một bài viết

Phần 6: Viết store procedure hiển thị danh sách bài viết

Phần 7: Viết store procedure tìm kiếm bài viết

Phần 8: Viết store procedure top 10 các bài viết mới và cũ

Tiếp theo chúng ta cần viết các hàm và Store Procedure cho bảng TB_News.

1. Store Procedure thêm, sửa, xóa một bài viết.

Cũng giống như với bảng TB_Category, mình viết spTB_News_Edit để thực hiện cả 3 hành động thêm, sửa, xóa một bài viết.
Nếu @Action=0 => xóa bài viết theo idNews=@idNews
Nếu @Action>0 =>
    Nếu @idNews=0 thí thêm mới bài viết
    Nếu @idNews>0 thì cập nhật bài viết theo idnews=@idnews

Store Procedure spTB_News_Edit  được viết như sau:

-- =============================================
-- Author:        webmaster@hmweb.com.vn
-- Description:   <Thêm, sửa hoặc xóa bài viết>\
-- =============================================
CREATE PROCEDURE [dbo].[spTB_News_Edit]
@Action int,
@idNews int,
@CateID int,
@UserName nvarchar(50),
@Title nvarchar(500),
@Description ntext,
@Content ntext,
@Author nvarchar(150),
@Picture nvarchar(255),
@CreateDate datetime,
@PublishDate datetime,
@Status int,
@Link nvarchar(150)
AS
BEGIN
       -- Nếu @Action=0 --> Xóa bài viết theo @idNews
       IF @Action=0
       BEGIN
            DELETE FROM TB_News WHERE idNews=@idNews
       END
       -- Nếu Nếu @Action=1 --> Thêm mới hoặc sửa  bài viết
       ELSE
            BEGIN
                  --Nếu @idNews=0 --> Thêm mới  bài viết
                  IF @idNews=0
                  BEGIN
                        INSERT INTO TB_News 
                        (
                              CateID, 
                              UserName, 
                              Title, 
                              Description, 
                              Content,
                              Author, 
                              Picture, 
                              CreateDate, 
                              PublishDate, 
                              Status, 
                              Link
                        ) 
                        VALUES
                        (
                              @CateID,
                              @UserName, 
                              @Title, 
                              @Description, 
                              @Content, 
                              @Author, 
                              @Picture, 
                              @CreateDate, 
                              @PublishDate, 
                              @Status, 
                              @Link
                        )
                  END
                  --Nếu @idNews>0 --> Cập nhật bài viết với idNews=@idNews
                  IF @idNews>0
                  BEGIN
                        UPDATE TB_News
                        SET
                              CateID = @CateID,
                              UserName = @UserName,
                              Title = @Title,
                              Description = @Description,
                              Content = @Content,
                              Author = @Author,
                              Picture = @Picture,
                              CreateDate = @CreateDate,
                              PublishDate = @PublishDate,
                              Status = @Status,
                              Link = @Link
                        WHERE idNews=@idNews
                  END
            END
END

2. Store procedure Xóa tất cả bài viết theo chuyên mục
Khi cần xóa hết bài viết của một chuyên mục bạn viết thủ tục như sau:

-- =============================================
-- Author:        webmaster@hmweb.com.vn
-- Description:   <Xóa bài viết theo chuyên mục được chọn>
-- =============================================
CREATE PROCEDURE [dbo].[spTB_News_Delete_ByCateID]
@CateID int
AS
BEGIN
      DELETE FROM TB_News WHERE CateID=@CateID
END

3. Store procedure Xóa tất cả bài viết của một UserName
Khi cần xóa hết bài viết của một UserName bạn viết thủ tục như sau:

Khi cần xóa hết bài viết của một UserName bạn viết thủ tục như sau:
 -- =============================================
-- Author:        webmaster@hmweb.com.vn
-- Description:   <Xóa hết bài viết theo UserName>
-- =============================================
CREATE PROCEDURE [dbo].[spTB_News_Delete_ByUserName]
@UserName nvarchar(50)
AS
BEGIN
      DELETE FROM TB_News WHERE UserName=@UserName
END