当第一步查询无数据时，整个存储过程的执行结果如下：
例 5 -调用存储过程的存储过程
这个例子中有2个存储过程。第一个存储过程uspFindContact查找第一条带有地址信息的记录并将ContactID返回给调用它的存储过程，然后显示人员和地址信息。
| CREATE PROCEDURE uspFindContact @LastName NVARCHAR(50), @ContactID INT output AS SELECT TOP 1 @ContactID = c.ContactID FROM HumanResources.Employee a INNER JOIN HumanResources.EmployeeAddress b ON a.EmployeeID = b.EmployeeID INNER JOIN Person.Contact c ON a.ContactID = c.ContactID INNER JOIN Person.Address d ON b.AddressID = d.AddressID WHERE c.LastName = @LastName |
下面的代码对uspGetContact做了一点修改：调用uspFindContact并返回结果集。
| ALTER PROCEDURE uspGetContact @LastName NVARCHAR(50) AS DECLARE @ContactID INT SET @ContactID = 0 EXEC uspFindContact @LastName=@LastName, @ContactID=@ContactID OUTPUT IF @ContactID <> 0 BEGIN SELECT ContactID, FirstName, LastName FROM Person.Contact WHERE ContactID = @ContactID SELECT d.AddressLine1, d.City, d.PostalCode FROM HumanResources.Employee a INNER JOIN HumanResources.EmployeeAddress b ON a.EmployeeID = b.EmployeeID INNER JOIN Person.Contact c ON a.ContactID = c.ContactID INNER JOIN Person.Address d ON b.AddressID = d.AddressID WHERE c.ContactID = @ContactID END ELSE BEGIN RAISERROR ('No record found',10,1) END EXEC uspGetContact @LastName='Walters' |
【责编:Zenghui】
相关文章
相关产品和培训
文章评论
专题推荐
今日更新 认证培训 频道精选
您现在的位置： 
2008-7-17
