/// liệt kê
USE Northwind;
GO
-- Create the stored procedure sp_[MaSinhVien]
CREATE PROCEDURE sp_15699
@Country NVARCHAR(50)
AS
BEGIN
DECLARE @CustomerID NVARCHAR(5),
@CustomerName NVARCHAR(40),
@CustomerAddress NVARCHAR(60),
@OrderQuantity INT,
@OrderIDs NVARCHAR(MAX);
-- Create a temporary table to store the results
CREATE TABLE #CustomerInfo (
CustomerID NVARCHAR(5),
CustomerName NVARCHAR(40),
CustomerAddress NVARCHAR(60),
OrderQuantity INT,
OrderIDs NVARCHAR(MAX)
);
-- Declare cursor to iterate through customers
DECLARE customerCursor CURSOR FOR
SELECT
[Link],
[Link],
[Link],
COUNT([Link]) AS OrderQuantity
FROM
Customers c
JOIN
Orders o ON [Link] = [Link]
JOIN
[Order Details] od ON [Link] = [Link]
WHERE
[Link] = @Country
GROUP BY
[Link],
[Link],
[Link]
ORDER BY
[Link];
OPEN customerCursor;
FETCH NEXT FROM customerCursor INTO @CustomerID, @CustomerName,
@CustomerAddress, @OrderQuantity;
WHILE @@FETCH_STATUS = 0
BEGIN
-- Initialize OrderIDs as an empty string
SET @OrderIDs = '';
-- Fetch OrderIDs for the current customer using FOR XML PATH
SELECT @OrderIDs = STUFF((SELECT '; ' + CAST([Link] AS NVARCHAR)
FROM Orders o
WHERE [Link] = @CustomerID
FOR XML PATH('')), 1, 2, '');
-- Insert into temporary table
INSERT INTO #CustomerInfo (CustomerID, CustomerName, CustomerAddress,
OrderQuantity, OrderIDs)
VALUES (@CustomerID, @CustomerName, @CustomerAddress,
@OrderQuantity,
@OrderIDs);
FETCH NEXT FROM customerCursor INTO @CustomerID, @CustomerName,
@CustomerAddress, @OrderQuantity;
END
CLOSE customerCursor;
DEALLOCATE customerCursor;
-- Select the results from the temporary table
SELECT * FROM #CustomerInfo;
-- Drop the temporary table
DROP TABLE #CustomerInfo;
END;
GO
-- Test the stored procedure
EXEC sp_15699 'USA';
GO
-----------------------------------------------------
USE Northwind;
GO
-- Create the table-valued function fn_[MaSinhVien]
CREATE FUNCTION fn_a45156 (@Country NVARCHAR(50))
RETURNS @CustomerInfo TABLE (
CustomerID NVARCHAR(5),
CustomerName NVARCHAR(40),
CustomerAddress NVARCHAR(60),
OrderQuantity INT,
OrderIDs NVARCHAR(MAX)
)
AS
BEGIN
DECLARE @CustomerID NVARCHAR(5),
@CustomerName NVARCHAR(40),
@CustomerAddress NVARCHAR(60),
@OrderQuantity INT,
@OrderIDs NVARCHAR(MAX);
-- Declare cursor to iterate through customers
DECLARE customerCursor CURSOR FOR
SELECT
[Link],
[Link],
[Link],
COUNT([Link]) AS OrderQuantity
FROM
Customers c
JOIN
Orders o ON [Link] = [Link]
JOIN
[Order Details] od ON [Link] = [Link]
WHERE
[Link] = @Country
GROUP BY
[Link],
[Link],
[Link]
ORDER BY
[Link];
OPEN customerCursor;
FETCH NEXT FROM customerCursor INTO @CustomerID, @CustomerName,
@CustomerAddress, @OrderQuantity;
WHILE @@FETCH_STATUS = 0
BEGIN
-- Initialize OrderIDs as an empty string
SET @OrderIDs = '';
-- Fetch OrderIDs for the current customer using FOR XML PATH
SELECT @OrderIDs = STUFF((SELECT '; ' + CAST([Link] AS NVARCHAR)
FROM Orders o
WHERE [Link] = @CustomerID
FOR XML PATH('')), 1, 2, '');
-- Insert into the table variable
INSERT INTO @CustomerInfo (CustomerID, CustomerName, CustomerAddress,
OrderQuantity, OrderIDs)
VALUES (@CustomerID, @CustomerName, @CustomerAddress,
@OrderQuantity,
@OrderIDs);
FETCH NEXT FROM customerCursor INTO @CustomerID, @CustomerName,
@CustomerAddress, @OrderQuantity;
END
CLOSE customerCursor;
DEALLOCATE customerCursor;
RETURN;
END;
GO
-- Test the table-valued function
SELECT * FROM fn_a45156('USA');
GO
--đây là function---------------------------
USE NorthWind;
GO
-- Tạo lại hàm table-valued function
CREATE FUNCTION fnGetSupplierProducts(@suppID INT)
RETURNS @ProductInfo TABLE
(
ProductID INT,
ProductName NVARCHAR(40),
KhoiLuongSP INT,
SoTienSP INT,
DSKH VARCHAR(1000)
)
AS
BEGIN
DECLARE @productID INT, @productName NVARCHAR(40), @KhoiLuongSP INT,
@SoTienSP INT;
DECLARE @DSKH VARCHAR(1000);
-- KhởPi tạo cursor đêP duyệt qua các saPn phâPm theo nhà cung câSp
DECLARE suppCursor CURSOR FOR
SELECT
[Link],
[Link],
SUM([Link]) AS KhoiLuongSP,
SUM([Link]) AS SoTienSP
FROM
Products p
JOIN
[Order Details] od ON [Link] = [Link]
WHERE
[Link] = @suppID
GROUP BY
[Link],
[Link]
ORDER BY
SoTienSP DESC;
OPEN suppCursor;
FETCH NEXT FROM suppCursor INTO @productID, @productName,
@KhoiLuongSP,
@SoTienSP;
WHILE @@FETCH_STATUS = 0
BEGIN
-- Reset danh sách khách hàng
SET @DSKH = '';
-- Truy xuâSt danh sách khách hàng cho mỗVi saPn phâPm sửP dụng FOR XML
PATH
đêP kêSt hợp chuỗVi
SELECT @DSKH = COALESCE(@DSKH + '; ', '') + [Link]
FROM
(
SELECT DISTINCT [Link]
FROM Orders o
JOIN [Order Details] od ON [Link] = [Link]
WHERE [Link] = @productID
) AS T;
-- Thêm vào baPng traP vêZ
INSERT INTO @ProductInfo (ProductID, ProductName, KhoiLuongSP, SoTienSP,
DSKH)
VALUES (@productID, @productName, @KhoiLuongSP, @SoTienSP, @DSKH);
FETCH NEXT FROM suppCursor INTO @productID, @productName,
@KhoiLuongSP,
@SoTienSP;
END
CLOSE suppCursor;
DEALLOCATE suppCursor;
RETURN;
END;
GO
SELECT * FROM fnGetSupplierProducts(1);
--đây là proc ----------------------------------------------------
Go
alter proc spStoreProcedure(@suppID int)
as
begin
declare @productID int, @productName varchar(40), @KhoiLuongSP int,
@SoTienSP int
declare suppCursor cursor for
select [Link], ProductName, sum([Link]) as KhoiLuongSP, sum(od.
[UnitPrice]) as SoTienSP
from Products p join [Order Details] od on [Link]=[Link]
where [Link]= @suppID
group by [Link], ProductName
order by SoTienSP desc
open suppCursor
FETCH NEXT FROM suppCursor INTO @productID , @productName, @KhoiLuongSP,
@SoTienSP
WHILE @@FETCH_STATUS=0
begin
print cast(@productID as varchar)+'; '+@productName+';
'+cast(@KhoiLuongSP as varchar)+'; '+cast(@SoTienSP as varchar)
.CustomerID
from Orders o join [Order Details] od on [Link]=[Link]
where ProductID=@productID) as T
insert into #tmp(ProductID,ProductName,KhoiLuongSP,SoTienSP,DSKH)
values(@productID,@productName,@KhoiLuongSP,@SoTienSP,@DSKH)
--print cast(@productID as varchar)+'; '+@productName+';
'+cast(@KhoiLuongSP as varchar)+'; '+cast(@SoTienSP as varchar)+@DSKH
FETCH NEXT FROM suppCursor INTO @productID , @productName,
@KhoiLuongSP, @SoTienSP
end
close suppCursor
deallocate suppCursor
select *
from #tmp;
end