I would expect that what you have should work.
create procedure t1
as
raiserror('this is my error', 16, 1)
go
create procedure t
@p int output
as
execute t1
if @@error != 0
set @p = -1
else
set @p = 0
return
go
declare @x int
execute t @x output
select @x
Are you finding the error in the Connection object's error collection?
-Mike
>Dear all,
>
>How can I return the value from StoredProc? The demo programs are showed as below:
>
>Stored Procedure:
>-----------------
>
>Create Procedure UPDMAIN
> @inval int, @instr varchar(10), @Err int output
>as
>
>begin
>
> select @inval = @inval + 1
> select @instr = 'Hello ' + @instr
>
> execute UPDMAIN1 @inval, @instr
>
> if @@Error <> 0
> @Err = -1
> else
> @Err = 0
>
>return @Err
>
>end
>
>
>Create Procedure UPDMAIN1
> @inval int, @instr varchar(255)
>as
>
>begin
> insert into tempdb (id, desc) values (@inval, @instr)
> /* Basically this procedure will generate the duplicate primary key error */
>end
>
>ASP Page
>--------
>
>Set cn = Server.CreateObject("ADODB.Connection")
>cn.open strconn
>Set cmd = Server.CreateObject("ADODB.Command")
>cmd.ActiveConnection = cn
>cmd.CommandText = "UPDMAIN"
>cmd.CommandType = adCmdStoredProc
>With cmd
>
> .Parameters.Append .CreateParameter("RTNVALUE", adInteger, adParamReturnValue, 4)
> .Parameters.Append .CreateParameter("@inval", adInteger, adParamInput, , 10)
> .Parameters.Append .CreateParameter("@instr", adVarChar, adParamInput, 10, "World!!")
> .Parameters.Append .CreateParameter("@Err", adInteger, adParamOutput, 3)
> .Execute
>
> Response.write("RTNVALUE = " & .Parameters("RTNVALUE").value )
> Response.write("@Err = " & .Parameters("@Err").value )
>End With
>
>cn.Close
>Set cn = Nothing
>
>Basically, the statement of UPDMAIN1 will generate a duplicate primary key error. The result of ASP showed BLANK value of "RTNVALUE" and "@Err". And UPDMAIN1 storedproc is a common stored procedure to share another procedure. Anyone has any idea? Thank you for your help ....
>
>Best Regards,
>
>Justy Chow
>(Stored Procedure Beginner)