help with INSERT in a SP

[Britt Jones]

The return highlighted exists the proc so if the insert into TermServer is successful the rest of the proc will not be executed.

What you want is

if @@error !=0
    begin
        return -1 --or additional error code
    end
 
 

William Boyer wrote:

I have a stored procedure that does an insert into 2 different tables. The
inserts work correctly, but it seems that anything after the final insert in
the SP doesn't get executed. Below is my SP. All lines after the INSERT INTO
NETDEVICES don't get executed.

create procedure sp_AddTermServer
     @host varchar(16),
     @ip varchar(16),
     @closet varchar(25),
     @location varchar(60),
     @comments varchar(60),
     @hubport varchar(6),
     @jacknbr varchar(16),
     @jobnumber char(10),
     @critical char(1),
     @numports smallint as

declare @gw varchar(16),
     @nettype varchar(6),
     @iplow varchar(16),
     @site varchar(3),
     @iphigh varchar(16)
/*
 Get information from the Closets table about this closet...
*/
select @gw = (select DefaultGW from Closets where Closet = @closet)
select @nettype = (select NetType from Closets where Closet = @closet)
/*
 Now insert into the TermServer table...
*/
insert TermServer(HostName,PortCount,ClosetRoomNbr,IPaddress,DeviceList)
             values(@host,@numports,@closet,@ip,space(16*7))
if @@error != 0
     return
/*
 And finally into the NetDevices table
*/
insert NetDevices
(Hostname,Facility,IPaddress,Location,ClosetRoomNbr,HubPort,Comments,HWtype,
DefaultGW,NetType,CriticalDevice,JackNbr)

Values(@host,substring(@host,1,3),@ip,@location,@closet,@hubport,@comments,'
TS',@gw,@nettype,@critical,@jacknbr)
if @@error != 0
     return
select HostName,IPaddress from NetDevices where hostname = @host
<< end of sp >>

The only thing I can think of is that there is an INSERT trigger defined for
the NetDevices table, but I can't see how that would terminate my SP.

Any help would be greatly appreciated.

Bill Boyer
mailto:wbo...@oz.net