How do I debug MSSQL 2005-2008

DECLARE @PersonsData nvarchar(250), @TempCount nvarchar(250), @AddressData nvarchar(250), @EmailData nvarchar(250), @TempStorage nvarchar(1024), @TempStorage2 nvarchar(1024);
 
 
DECLARE cursor_siteper CURSOR GLOBAL
FOR SELECT SitePersonnelID AS perIDList
FROM SitePersonnel
OPEN cursor_siteper;
 
FETCH NEXT FROM cursor_siteper INTO @TempCount;
 
WHILE @@FETCH_STATUS = 0
BEGIN
 
 
set @PersonsData = 'INSERT INTO Persons (LastName, Firstname, MiddleName, yearofbirth ) SELECT [BackupKeySymSQL_ltd].[dbo].[SitePersonnel].LastName, Firstname, Middlename, Birthday FROM [Backup_ltd].[dbo].[SitePersonnel] WHERE SitePersonnelID = ' + @TempCount + '';
 
Set @TempStorage2 = 'SELECT MAX(SID) AS TempStorage FROM persons';
 
set @AddressData = 'INSERT INTO Addresses (SID, AddressPrimary, AddressLine1, AddressLine2, City, State, Country, PostalCode, Phone, PhoneExt, Fax, JobTitle) ' + @TempStorage + ', 1, Select [Backup_ltd].[dbo].[SitePersonnel].addrline1, addrline2, City, State, Country, PostalCode, Phone, PhoneExt, Fax, JobTitle FROM [Backup_ltd].[dbo].[SitePersonnel] WHERE SitePersonnelID = ' + @TempCount + '';
 
set @EmailData = 'INSERT INTO Emails (SID, EmailPrimary, EmailAddress) ' + @TempStorage + ', 1, Select [Backup_ltd].[dbo].[SitePersonnel].Email WHERE SitePersonnelID = ' + @TempCount + '';
 
SET IDENTITY_INSERT Persons OFF
BEGIN TRANSACTION
BEGIN TRY
exec sp_executesql @PersonsData;
exec sp_executesql @TempStorage2;
print @TempStorage;
exec sp_executesql @AddressData;
exec sp_executesql @EmailData;
END TRY
BEGIN CATCH
	SELECT
		ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() as ErrorState,
        ERROR_PROCEDURE() as ErrorProcedure,
        ERROR_LINE() as ErrorLine,
        ERROR_MESSAGE() as ErrorMessage;
		IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH;
 
	IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;
SET IDENTITY_INSERT Persons ON
FETCH NEXT FROM cursor_siteper INTO @TempCount;
END
CLOSE cursor_siteper
DEALLOCATE cursor_siteper
GO

Select all Open in new window