SQL stored procedure usage experience

 
Example 1: query the stored procedure of all data in a data table

Query the Stored Procedure statement in Analyzer:
Declare @ retval int
Execute sp_showalluserinfo @ retval

Stored Procedure statement in Enterprise Manager:
Create procedure sp_showalluserinfo @ retval int output
As
Select * From userlist
If @ rowcount <= 0
Return 0
Else
Return 1
Go

 

Example 2: query the stored procedure of a specified field in a data table

Query the Stored Procedure statement in Analyzer:
Execute sp_searchuserinfo null, 'xqf222 @ 163.com'

Stored Procedure statement in Enterprise Manager:
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