设一个OUTPUT变量来返回异常到DELPHI中或RAISEERROR出来。
1.
create PROCEDURE ... @msg varchar(100) output
set @msg=''IF @@error<>0
BEGIN
ROLLBACK TRAN
set @msg='清除原纱留位记录不成功!'
RETURN
END 2.
IF @@error<>0
BEGIN
ROLLBACK TRAN
RaisError('清除原纱留位记录不成功!',16,1)
RETURN
END
1.
create PROCEDURE ... @msg varchar(100) output
set @msg=''IF @@error<>0
BEGIN
ROLLBACK TRAN
set @msg='清除原纱留位记录不成功!'
RETURN
END 2.
IF @@error<>0
BEGIN
ROLLBACK TRAN
RaisError('清除原纱留位记录不成功!',16,1)
RETURN
END
如果谁有捕获存储过程错误的办法请告之,仅使用DataBase和StoredProc控件
下面是一个修改数据库密码的SP,也许会对您有帮助:
CREATE PROCEDURE dbo.password
@old varchar(30) = NULL, /* the old (current) password */
@new varchar(30), /* the new password */
@loginame varchar(30) = NULL /* user to change password on */
as
declare @suid int /* suid of person to change pw on */
/*
** If we're in a transaction, disallow this since it might make recovery
** impossible.
*/
if @@trancount > 0
begin
raiserror(15002,-1,-1,'sp_password')
return (1)
end
/*
** If the system administrator (sa) is running this command, the sa can
** change anyone's password and doesn't have to know the old password.
** We have this case is suser_id() = 1 and @loginame is NOT NULL.
** In this case, we'll fill in the old password and set the suid.
*/
if suser_id() <> 1 and @loginame IS NOT NULL
begin
/*
** Only the sa can use the @loginame.
*/
raiserror(15210,-1,-1)
return (1)
end
/*
** Does the SA want to change someone's password?
*/
if suser_id() = 1 and @loginame IS NOT NULL
begin
/* Check to seeif the user exists. */
select @suid = suid from master..syslogins where name = @loginame
if @suid is null
begin
raiserror(15007,-1,-1,@loginame)
return (1)
end
/*
** Get the old password for the user. If @old not given, don't
** bother checking against the old password.
*/
if @old IS NOT NULL
begin
/*
** Reinitialize @suid since previous assignment
** set it.
*/
select @suid = NULL
select @suid = suid
from master..syslogins
where name = @loginame
and (password = @old or
pwdcompare(@old, password) = 1
)
if @suid is null
begin
raiserror(15211,-1,-1)
return (1)
end
end
end
/*
** This is just the normal case when someone wants to change their own
** password. Set the @suid.
*/
else
if @loginame is null
select @suid = suid
from master..syslogins
where (password = @old or pwdcompare(@old, password) = 1)
and suid = suser_id()
if @suid is null
begin
raiserror(15211,-1,-1)
return (1)
end
/*
** This is the final case. The sa is running the command to change
** someone else's password and is supplying the old password. Confirm
** that the old password is correct.
*/
if suser_id() = 1
begin
select @suid = suid
from master..syslogins
where (password = @old or pwdcompare(@old, password) = 1)
and suid = suser_id(@loginame)
if @suid is null
begin
raiserror(15211,-1,-1)
return (1)
end
end
/*
** If @suid still hasn't been set then the user running the procedure
** doesn't have the right current password.
*/
if @suid is null
begin
raiserror(15211,-1,-1)
return (1)
end
/*
** Everything is consistent so change the password and set encrypt flag.
*/
update master..syslogins set password = pwdencrypt(@new),status = status | 8
where suid = @suid
print 'Password changed.'
return (0)