/*SP1*/
CREATEPROCEDUREdbo.getUserList
as
setnocounton
begin
select*fromdbo.[userinfo]
end
go
以上存储历程获得userinfo表中的一切纪录,前往一个纪录集。经由过程command工具挪用该存储历程的ASP代码以下:
/*SP3*/
CREATEPROCEDUREdbo.delUserAll
as
setnocounton
begin
BEGINTRANSACTION
deletefromdbo.[userinfo]
IF@@error=0
begin
COMMITTRANSACTION
return1
end
ELSE
begin
ROLLBACKTRANSACTION
return0
end
return
end
go
以上存储历程,在delete顺遂实行时,前往1,不然前往0,并举行回滚操纵。为了在ASP中获得前往值,必要使用Parameters汇合来声明参数:
/*SP4*/
CREATEPROCEDUREdbo.getUserName
@UserIDint,
@UserNamevarchar(40)output
as
setnocounton
begin
if@UserIDisnullreturn
select@UserName=username
fromdbo.[userinfo]
whereuserid=@UserID
return
end
go
挪用该存储历程的ASP代码以下:
/*SP5*/
CREATEPROCEDUREdbo.getUserName
--为了加深对"按次"的印象,将以下两参数的界说按次倒置一下
@UserNamevarchar(40)output,
@UserIDint
as
setnocounton
begin
if@UserIDisnullreturn
select@UserName=username
fromdbo.[userinfo]
whereuserid=@UserID
if@@rowcount>0
return1
else
return0
return
end
go
/*SP7*/
CREATEPROCEDUREdbo.getUserInfo
@useridint,
@checkloginbit
as
setnocounton
begin
if@useridisnullor@checkloginisnullreturn
selectusername
fromdbo.[usrinfo]
whereuserid=@userid
--若为登任命户,取usertel及usermail
if@checklogin=1
selectusertel,usermail
fromdbo.[userinfo]
whereuserid=@userid
return
end
go
以下是ASP代码: