stored procedures, error handling question

[belcom125]

I am just starting to write stored procedures and I wrote one that is fairly complex (I think). I query the sysobjects and syscolums tables and then I input results into a temp table and then I have to update some fields in all selected tables based on certain criteria. Anyway my question is... I was told to use error handling and logs (like Raiserror and With LOG). I'm not sure where to use that. What are best practices to using error checking? Oh and also I wanna use BEGIN TRANSACTION and ROLLBACK in case something goes wrong during udpdate.

Here is my script:

--Drop temporary tables if they already exist
drop table #binfilter
drop table #whfilter

--Create temp tables for to filter out distinct records
create table #binfilter (id int, tablename varchar(50))
create table #whfilter (id int, tablename varchar(50))

--Select tables that contain BinID Field and insert the results into the temp table #binfilter
insert into #binfilter (ID, Tablename)
select so.id as ID, so.name as TableName from sysobjects so
inner join syscolumns sc on so.id = sc.id
where so.type = 'U'
and sc.name in ('BinID')

--Select tables that contain WarehouseID Field and insert the results into the temp table #whfilter
insert into #whfilter (ID, Tablename)
select so.id as ID, so.name as TableName from sysobjects so
inner join syscolumns sc on so.id = sc.id
where so.type = 'U'
and sc.name in ('warehouseid')

-- Declare host variables
DECLARE @table_name varchar(50)
DECLARE @wh_update varchar(2000)
DECLARE @binid varchar(20)
DECLARE @whid varchar(20)
set @binid = '306'
set @whid = '1'
-- Declare the cursor
DECLARE tnames_cursor CURSOR LOCAL FORWARD_ONLY
FOR
SELECT w.tablename FROM #whfilter w
INNER JOIN #binfilter b ON w.id = b.id

-- Open the cursor
OPEN tnames_cursor

-- Fetch the first row in the cursor
FETCH NEXT FROM tnames_cursor INTO @table_name
WHILE @@FETCH_STATUS = 0

-- While the fetch is successful
BEGIN
if (len(@table_name) > 0)
BEGIN
--Here goes the update statement
BEGIN TRANSACTION
select @wh_update = 'UPDATE '+ @table_name +
' SET '+ @table_name +'.warehouseid =' + @whid + ' FROM '+ @table_name +
' WHERE '+ @table_name +'.binid = '+ @binid

exec (@wh_update)
END

-- Fetch next product
FETCH NEXT FROM tnames_cursor INTO @table_name

END

-- Close the cursor
CLOSE tnames_cursor

-- Deallocate the cursor
DEALLOCATE tnames_cursor