Trong bài này, mình sẽ hướng dẫn cách bạn cách truyền tham số vào Stored Procedure trong Power Query.
Trong ví dụ minh họa, chúng ta sẽ sử dụng hàm uspGetBillOfMaterials trong DB AdventureWorks 2012. Hàm này sử dụng bằng cách truyền vào 2 tham số ProductID và CheckDate.
Sử dụng SSMS để chạy thử hàm này:
exec [dbo].[uspGetBillOfMaterials] 727, ’2009-01-02′
Sau đó chúng ta sẽ có được kết quả như hình dưới đây:
Bây giờ chúng ta sẽ chuyển sang công cụ Power Query để thực hiện. Mở Excel, chuyển sang tab Power Query và chọn SQL Server
Nhập tên Server, Database và SQL statement như hình bên dưới:
Click ô Name để đặt tên cho query là GetBOM. Sau đó, click "Close and Load" ở tab Home.
Đợi 1 chút, dữ liệu sẽ được load vào trong worksheet của Excel. Việc chính chúng ta cần làm là làm sao có thể truyền tham số vào stored proc. Bạn có thể thấy rằng chúng ta sẽ truyền vào hàm trên 2 tham số, 1 tham số kiểu Integer và 1 tham số kiểu Date. Tiếp theo, ta sẽ tạo 1 table mới với 2 column chứa các tham số. Table này sẽ được đặt tên là param.
Trong ví dụ minh họa, chúng ta sẽ sử dụng hàm uspGetBillOfMaterials trong DB AdventureWorks 2012. Hàm này sử dụng bằng cách truyền vào 2 tham số ProductID và CheckDate.
Sử dụng SSMS để chạy thử hàm này:
exec [dbo].[uspGetBillOfMaterials] 727, ’2009-01-02′
Sau đó chúng ta sẽ có được kết quả như hình dưới đây:
Bây giờ chúng ta sẽ chuyển sang công cụ Power Query để thực hiện. Mở Excel, chuyển sang tab Power Query và chọn SQL Server
Nhập tên Server, Database và SQL statement như hình bên dưới:
Click ô Name để đặt tên cho query là GetBOM. Sau đó, click "Close and Load" ở tab Home.
Đợi 1 chút, dữ liệu sẽ được load vào trong worksheet của Excel. Việc chính chúng ta cần làm là làm sao có thể truyền tham số vào stored proc. Bạn có thể thấy rằng chúng ta sẽ truyền vào hàm trên 2 tham số, 1 tham số kiểu Integer và 1 tham số kiểu Date. Tiếp theo, ta sẽ tạo 1 table mới với 2 column chứa các tham số. Table này sẽ được đặt tên là param.
Cột Check Date sẽ được chuyển định dạng sang Text.
Trở lại tab Power Query, trong Workbook Queries, right click GetBOM và chọn Edit
Tại cửa sổ GetBOM Query Editor, chuyển sang tab View và chọn Advanced Editor.
Chúng ta sẽ chỉnh sửa script tại đây. Tất cả việc cần làm là truyền biến được lấy từ bảng param được định nghĩa khi nãy. Script dưới đây để ta sẽ đọc dữ liệu cell có vị trí nằm ở bảng param.
Excel.CurrentWorkbook(){[Name="TABLE_NAME"]}[Content]{ROW_NUMBER}[#”COLUMN_NAME”],
Code trên ta có: TABLE_NAME là param, ROW_NUMBER là số thứ tự dòng để load và COLUMN_NAME là tên cột. Như vậy, để lấy được giá trị đầu tiên của cột ProductID thuộc bảng param, ta có đoạn code sau:
Excel.CurrentWorkbook(){[Name="param"]}[Content]{0}[#”ProductID”],
Tương tự như vậy, ta có:
Excel.CurrentWorkbook(){[Name="param"]}[Content]{0}[#”Check Date”],
Bây giờ chúng ta sẽ thay thế đoạn code cũ bằng đoạn code mới như sau cho việc truyền tham số. Bạn copy paste vào Advanced Editor:
let
ProductID=Excel.CurrentWorkbook(){[Name="param"]}[Content]{0}[#"ProductID"],
CheckDate=Excel.CurrentWorkbook(){[Name="param"]}[Content]{0}[#"Check Date"],
Source = Sql.Database(“SQL_SERVER_INSTANCE NAME“, “AdventureWorks2012″,
[Query="exec [dbo].[uspGetBillOfMaterials] ‘”
& Number.ToText(ProductID)
& “‘, ‘”
& CheckDate
& “‘”])
in
Source
ProductID=Excel.CurrentWorkbook(){[Name="param"]}[Content]{0}[#"ProductID"],
CheckDate=Excel.CurrentWorkbook(){[Name="param"]}[Content]{0}[#"Check Date"],
Source = Sql.Database(“SQL_SERVER_INSTANCE NAME“, “AdventureWorks2012″,
[Query="exec [dbo].[uspGetBillOfMaterials] ‘”
& Number.ToText(ProductID)
& “‘, ‘”
& CheckDate
& “‘”])
in
Source
Đừng quên thay tên SQL Server Intance theo thông số của bạn nhé.
Lưu ý 1 số điểm theo hình bên dưới:
Để thực hiện nối chuỗi trong đoạn code trên, ta sẽ sử dụng "&". Sau đó click Done và click Close and Load ở tab Home.
Bây giờ chúng ta sẽ thay đổi giá trị ở bảng param và refresh dữ liệu, để xem kết quả mới trong Excel nhé. Ta sẽ đổi ProductID từ 727 thành 800 sau đó click Refresh Data. Xuất hiện cửa sổ mới như hình dưới đây:
Biến đầu tiên truyền vào được đổi thành 800, Click Run để check xem sao nhé.
Nhận xét: Đây là 1 trong những cách "work around" cho mục đích truyền tham số vào 1 hàm hay một câu Select query , thủ thuật này được nghiên cứu và chia sẽ bởi SOHEIL BAKHSHI. Với công cụ Power Query, chúng ta có thể lấy dữ liệu từ nhiều nguồn bằng SQL query, Store Proc. Việc truyền tham số vào các hàm hỗ trợ linh hoạt cho những nhu cầu xây dựng các báo cáo đa dạng và phức tạp hơn.
