Những thói quen tốt khi viết câu lệnh T-SQL- P5

Tham khảo tài liệu những thói quen tốt khi viết câu lệnh t-sql- p5, công nghệ thông tin, cơ sở dữ liệu phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả
Nội dung trích xuất từ tài liệu:
Những thói quen tốt khi viết câu lệnh T-SQL- P5SalesOrderID = @SalesOrderID IF @SalesOrderDetailID IS NOT NULL SET @WHERE = @WHERE + ANDSalesOrderDetailID =@SalesOrderDetailID IF @CarrierTrackingNumber IS NOTNULL SET @WHERE = @WHERE + ANDCarrierTrackingNumber =@CarrierTrackingNumber IF LEN(@WHERE) > 0 SET @CMD = @CMD + WHERE +RIGHT(@WHERE,LEN(@WHERE) - 3)EXEC sp_executesql @CMD , N@SalesOrderIDint,@SalesOrderDetailID int,@CarrierTrackingNumbernvarchar(25) ,@SalesOrderID =@SalesOrderID,@SalesOrderDetailID =@SalesOrderDetailID,@CarrierTrackingNumber =@CarrierTrackingNumberTiếp theo chúng ta sẽ đi sâu vào chi tiết nhằm giúpbạn hiểu rõ phần động và phần thông số hóa của đoạnmã trên. SP này bắt đầu bằng việc gán biến @CMDvào câu lệnh SELECT không có mệnh đề WHERE.Tiếp theo ta gán biến @WHERE cho một chuỗi rỗng.Tiếp đó là bốn câu IF khác nhau. Ba câu IF đầu tiênkiểm tra xem mỗi tham số truyền vào có thỏa mãnđiều kiện NOT NULL hay không. Nếu một tham sốNOT NULL, ta sẽ gắn điều kiện vào biến @WHEREđối với tham số ấy. Do ta đã kiểm tra và xác địnhtham số đó NOT NULL, ta không cần thêm điều kiệnIS NULL vào mệnh đề WHERE như đoạn code SPban đầu ở phần trên. Thay vào đó, tất cả những gì tacần là thêm điều kiện = @PARMvào biến @WHERE. Câu If cuối cùng xác định xembiến @WHERE có thỏa mãn ít nhất một điều kiệnhay không, và nếu có thì nó sẽ nối biến @WHEREvới biến @CMD.Lưu ý rằng biến @WHERE là phần động của đoạncode. Nhưng tôi không đặt phần text thực của thamsố vào biến @WHERE, thay vào đó chỉ đặt một thamchiếu tới các tham số trong điều kiện WHERE. Vì thếcâu lệnh T-SQL động cơ bản chỉ bao gồm câu lệnhSELECT ban đầu và mệnh đề WHERE không còncần điều kiện IS NULL để ràng buộc dữ liệu nữa.Cuối cùng tôi sử dụng SP “sp_executesql” để thực thicâu lệnh T-SQL động thông số hóa. Để thực hiệnđiều này, tôi truyền năm tham số vào SP hệ thống.Tham số đầu tiên là biến T-SQL động @CMD. Thamsố thứ hai khai báo tất cả các biến có thể có trongđoạn truy vấn được thông số hóa, cùng với loại dữliệu của chúng. Với ba tham số cuối cùng, chúng chỉđược truyền vào SP hệ thống giống như chúng đượctruyền vào SP lưu trữ đa năng trong phần đầu. Nhưbạn có thể thấy, tôi hoàn toàn không làm đoạn mãSQL động của mình trở nên dễ bị tấn công bằng SQLinjection hơn SP ban đầu. Lý do là vì tôi không sửdụng giá trị thực của tham số để chuyển tới biến@WHERE. Tôi chỉ truyền tham số như các biến vàoSQL động qua SP hệ thống “sp_executesql”.Bây giờ hãy chạy đoạn code tạo thủ tục lưu trữ đanăng mới viết lại bằng cách chạy câu lệnh sau:EXEC JackOfAllTrades_V2@SalesOrderID = 43659Khi chạy thử nghiệm với cơ sở dữ liệuAdventureWorks trên server, tôi nhận được sơ đồthực thi như sau:Khi so sánh sơ đồ này với sơ đồ ở phần đầu, bạn cóthể thấy nó đơn giản hơn và sử dụng thao tác “tìmkiếm chỉ mục cụm” để xử lý SP. Sở dĩ máy chủ SQLcó thể sử dụng thao tác này là vì đoạn code SQLđộng không còn điều kiện “@PARM IS NULL” nữa.Do câu lệnh T-SQL đã được đơn giản hóa nhờ sửdụng SQL động và loại bỏ ràng buộc IS NULL, máychủ SQL giờ đây có thể đưa ra kế hoạch thực thi tốiưu hơn cho thủ tục lưu trữ đa năng phiên bản V2.Kết lại, thực tế thì hiệu quả chúng ta thu được ở mứcnào? Nên nhớ trên đây ta chỉ mới xét những bản ghitừ bảng SalesOrderDetail có SalesOrderID bằng43659. SP lưu trữ đa năng ban đầu sử dụng thao tác