ch04 sql 创建方法

--8-1
USE Northwind
SELECT * from ::fn_dblog('', '')
GO
--8-2
USE Northwind
SELECT * from ::fn_dblog('', '') WHERE [Begin Time] >= '02/01/07'
GO
--9-1
SELECT *
from master.dbo.sysprocesses
--9-2
SELECT *
from sys.dm_exec_requests

--9-3
DECLARE @Handle varbinary(64);
SELECT @handle = sql_handle
from master.dbo.sysprocesses
WHERE spid = @@SPID
SELECT * from ::fn_get_sql(@Handle);
GO
--10-1 方法
USE Northwind
GO
CREATE FUNCTION dbo.CountOFProductsByCategory(@pCategoryID INT)
RETURNS INT
AS
BEGIN
RETURN (
SELECT COUNT(*)
from dbo.Products
WHERE CategoryID = @pCategoryID
)
END
GO
--10-2
SELECT COUNT(*) from Products
WHERE CategoryID ='1'
GO
PRINT '属于产品类型的产品有'+CONVERT(VARCHAR(3),dbo.CountOFProductsByCategory(1))+'种。'
GO
--10-3
USE Northwind
GO
PRINT '属于产品类型的产品有' PRINT '属于产品类型的产品有'+CONVERT(VARCHAR(3),dbo.CountOFProductsByCategory(DEFAULT))+'种。'
GO
--11-1 方法返回 TABLE
USE Northwind
GO
CREATE FUNCTION dbo.ProductsByCategory(@pCategoryID INT)
RETURNS TABLE
AS
RETURN
SELECT ProductID,CategoryID,ProductName,UnitPrice
from dbo.Products
WHERE CategoryID = @pCategoryID
GO
--11-2
USE Northwind
GO
Select * from DBO.ProductsByCategory('1')
--12-1 方法返回 TABLE2
USE Northwind
GO
CREATE FUNCTION dbo.CategoryInfo()
RETURNS @CategoryInfo TABLE
(
CategoryID INT,
NumberOfProducts INT
)
AS
BEGIN
DECLARE @lminCID INT
DECLARE @lmaxCID INT
DECLARE @CountsOfRec INT


SELECT @lminCID =MIN(Categoryid) from Products
SELECT @lmaxCID =MAX(Categoryid) from Products
SELECT @CountsOfRec = Count(*) from Products
WHERE CategoryID = @lminCID
WHILE @lminCID <= @lmaxCID
BEGIN
INSERT INTO @CategoryInfo
VALUES(@lminCID,@CountsOfRec)
SET @lminCID =@lminCID+1
SELECT @CountsOfRec = Count(*) from Products
WHERE CategoryID = @lminCID
END
RETURN
END

--12-2
SELECT * from dbo.CategoryInfo()
--12-3