How can I improve the following stored procedure?
Here are some comments in no particular order:


Your CATCH block - including comments - is a copy and paste from the documentation. While it's always good to read the documentation, please remember that the examples are there to illustrate specific features and you shouldn't assume that they're usable as-is in production code
If an error occurs in the TRY block, your CATCH block commits if possible anyway, is this what you intended? XACT_STATE() tells you that the transaction can be committed, not that it should be. See this SO question for more information.
Your CATCH block doesn't examine or log the error message (using ERROR_NUMBER() and similar functions), so if an error does occur you will have no idea what it was
Always list all column names in an INSERT; this gives you some security if the table structure changes
You cannot compare NULL values with the standard comparison operators, you need to write IF @URI IS NULL instead
Always use schema names to reference objects in code
Using BEGIN and END to wrap the entire stored procedure body is not required but usually good practice, otherwise it's easy to forget where the procedure ends and accidentally add some code that you didn't mean to
Most procedures should SET NOCOUNT ON at the beginning


And please always mention your SQL Server version (2005, 2008, 2008R2, 2012) and edition (Standard, Enterprise, Express) because that determines which language features are available.