ch 13 - stored procedures.sql

《SQLServer2000宝典》—包括本书中的所有代码

-----------------------------------------------------------
-- SQL Server 2000 Bible 
-- Wiley Publishing 
-- Paul Nielsen

-- Chapter  13 - Developing Stored Procedures

-----------------------------------------------------------
-----------------------------------------------------------

---------------------------------------------
-- Create Alter Drop

USE OBXKites
go

CREATE PROCEDURE CategoryList
AS 
SELECT ProductCategoryName, ProductCategoryDescription  
  FROM dbo.ProductCategory
go

EXEC CategoryList

-- Compile

EXEC sp_recompile CategoryList

SELECT CAST(C.sql as Char(35)) as StoredProcedure, cacheobjtype,  usecounts as Count, ObjType
  FROM Master.dbo.syscacheobjects C
  JOIN  Master.dbo.sysdatabases D
    ON C.dbid = C.dbid
  WHERE D.Name = DB_Name() 
    AND ObjType = 'Proc'
  ORDER BY StoredProcedure

-- Encryption

sp_helptext CategoryList

ALTER PROCEDURE CategoryList
WITH ENCRYPTION
AS 
SELECT * 
  FROM dbo.ProductCategory

sp_helptext CategoryList

----------------------------------------------------
-- Parameters

USE OBXKites

go
CREATE PROCEDURE CategoryGet
  (@CategoryName NVARCHAR(35))
AS
SELECT ProductCategoryName, ProductCategoryDescription 
  FROM dbo.ProductCategory
  WHERE ProductCategoryName = @CategoryName
go

EXEC CategoryGet 'Kite'

-- Default

CREATE PROCEDURE pProductCategory_Fetch(
  @Search NVARCHAR(50) = NULL
)
-- If @Search = null then return all ProductCategories
-- If @Search is value then try to find by Name
AS 
  SET NOCOUNT ON
  SELECT ProductCategoryName, ProductCategoryDescription
    FROM dbo.ProductCategory
    WHERE ProductCategoryName = @Search
      OR @Search IS NULL
  IF @@RowCount = 0  
    RAISERROR('Product Category ''%s'' Not Found.',14,1,@Search)
go

EXEC pProductCategory_Fetch 'OBX'

EXEC pProductCategory_Fetch

-- Output Parameters

-- create simple proc with output parameter
USE OBXKites
go
CREATE PROC GetProductName (
  @ProductCode CHAR(10),
  @ProductName VARCHAR(25) OUTPUT )
AS
SELECT @ProductName = ProductName
  FROM dbo.Product
  WHERE Code = @ProductCode
go

-- calling batch

USE OBXKITES
DECLARE @ProdName VARCHAR(25)
EXEC GetProductName '1001', @ProdName OUTPUT
PRINT @ProdName

--Return

CREATE PROC IsItOK (
  @OK VARCHAR(10) )
AS
IF @OK = 'OK'
  RETURN 0
ELSE 
  RETURN -100
go


DECLARE @ReturnCode INT
EXEC @ReturnCode = IsITOK 'OK'
PRINT @ReturnCode
EXEC @ReturnCode = IsItOK 'NotOK'
PRINT @ReturnCode

---------------------------------------------------
-- Using Stored Procedures within Queries

SELECT * FROM OpenQuery(
  NOLI, 
  'EXEC OBXKites.dbo.pProductCategory_Fetch') 
  WHERE ProductCategoryDescription Like '%stuff%'

---------------------------------------------------
-- Remote Stored Procedures

EXEC [Noli\SQL2].OBXKites.dbo.pProductCategory_AddNew 'Food', 'Eatables'

SELECT CustomerTypeName, DiscountPercent, [Default]
  FROM OPENQUERY(
    [Noli\SQL2], 
    'OBXKites.dbo.pCustomerType_Fetch' )