实际例子1:查询数据表中所有数据的存储过程
查询分析器中测试存储过程语句:
declare @RetVal int
execute sp_ShowAllUserInfo @RetVal
企业管理器中的存储过程语句:
CREATE PROCEDURE sp_ShowAllUserInfo @RetVal int OUTPUT
AS
Select * from UserList
if @@ROWCOUNT<= 0
return 0
else
return 1
GO
实际例子2:查询数据表中指定字段内容的存储过程
查询分析器中测试存储过程语句:
execute sp_SearchUserInfo null,'xqf222@163.com'
企业管理器中的存储过程语句:
CREATE PROCEDURE sp_SearchUserInfo
(
@UserName nvarchar(20),
@UserEmail nvarchar(60)
)
AS
if Len(@UserName)=0 or Len(@UserEmail)=0
Select * from UserList
else
if Len(@UserName)>0 and Len(@UserEmail)>0
Select * from UserList where UserName=@UserName
else
if Len(@UserName)>0
Select * from UserList where UserName=@UserName
else
Select * from UserList where UserEmail=@UserEmail
GO