01/10/2018, 14:20

SQL Server - In dữ liệu trong Stored Procedure theo dạng bảng

Cho mình có cách nào để nào để dùng hàm PRINT trong stored procedure để in dữ liệu theo dạng bảng không ? ví dụ như

Đây là code stored procedure của mình

/*
3.print order by order id
*/
USE fruit_shop
GO

ALTER PROCEDURE dbo.usp_printOrderByOrderId
@order_id INT
AS
BEGIN

DECLARE @order_date DATETIME
DECLARE @staff_first_name NVARCHAR(20)
DECLARE @staff_last_name NVARCHAR(20)
DECLARE @customer_first_name NVARCHAR(20)
DECLARE @customer_last_name NVARCHAR(20)
DECLARE @total_money FLOAT

-- print basic information --
SELECT TOP 1  
		@order_date = order_date,
		@staff_first_name = staff.first_name,
		@staff_last_name = staff.last_name,
		@customer_first_name = customer.first_name,
		@customer_last_name = customer.last_name,
		@total_money = SUM(order_detail.quantity*order_detail.price)
FROM [order]
JOIN staff ON staff.id = staff_id
JOIN customer ON customer.id = customer_id
JOIN order_detail ON order_detail.order_id = [order].id
WHERE [order].id = @order_id
GROUP BY [order].id, order_date, staff.first_name, staff.last_name, customer.first_name, customer.last_name

PRINT N'Order date: ' + CONVERT(VARCHAR(19), @order_date, 120);
PRINT N'Staff: ' + @staff_last_name + ' ' + @staff_first_name;  
PRINT N'Customer: ' + @customer_last_name + ' ' + @customer_first_name;

-- print detail --
PRINT N'=========================================================================================================================='
PRINT N'Product name                 Quantity           Price           Money'`
PRINT N'=========================================================================================================================`
DECLARE @product_name NVARCHAR(100)
DECLARE @quantity FLOAT
DECLARE @price FLOAT
DECLARE @detail_total_money FLOAT
DECLARE @order_detail_id INT

DECLARE DetailCursor CURSOR FOR
SELECT 
	product.name, 
	quantity, 
	price, 
	SUM(quantity * price),
	order_detail.id
FROM order_detail
JOIN [order] ON order_detail.order_id = [order].id
JOIN [product] ON product.id = product_id
WHERE [order].id = @order_id
GROUP BY product.name, quantity, price, order_detail.id
OPEN DetailCursor
FETCH NEXT FROM DetailCursor INTO @product_name, @quantity, @price, @detail_total_money, @order_detail_id
	WHILE @@FETCH_STATUS = 0
		BEGIN
		PRINT @product_name + '                 ' + CAST(@quantity AS VARCHAR) + '           ' + CAST(@price AS VARCHAR) + '           ' + CAST(@detail_total_money AS VARCHAR);   
		FETCH NEXT FROM DetailCursor INTO @product_name, @quantity, @price, @detail_total_money, @order_detail_id
		END
CLOSE DetailCursor
DEALLOCATE DetailCursor


-- print total money --
PRINT N'--------------------------------------------------------------------------------------------------------------------------'
PRINT N'Total money: ' + CAST(@total_money AS VARCHAR); 
  

END
GO

EXEC dbo.usp_printOrderByOrderId 33

Đây là kết quả

Order date: 2018-01-28 09:28:35
Staff: Tran Thi Thu Thao
Customer:  guest
==========================================================================================================================
Product name                 Quantity           Price           Money
==========================================================================================================================
Chuoi gia                 0.5           15000           7500
Dua hau khong hat                 1           14000           14000
Chuoi gia                 0.5           15000           7500
--------------------------------------------------------------------------------------------------------------------------
Total money: 29000

Cảm ơn mọi người trước ạ !

Bài liên quan
0