SQL stored procedure for fruit checks with several joins
There are a couple of things slowing you down. 


Two Left Outer Joins and a Full Outer Join. 

There may not be much you can do about this unless you can trim down the fields that need to be returned. Start with trying to remove the Full Outer Join. It will have worse performance than the Left Outer Joins. 
Scanning an entire field will also be a bit on the slow side, but again, there may be little you can do beyond making sure there are proper indices on the table if this is truly a requirement. 


LIKE '%' + @CheckUrlFilter + '%' 



It looks very much like this is a requirement. 
It's poor practice to select all fields from a table using the table.* notation. Doing this will force the RDBMS to lookup the table's meta data. I've found that it's pretty rare that I'll need every column from a table. It's best to be explicit about what you're selecting, even if you're selecting everything. Any performance improvement from this will be marginal, but it has the benefit of protecting your code from breaking if the underlying table changes. 




I don't know enough about the data or requirements to give any real concrete advice, but nixing that Full Outer Join is the best bet for getting a performance boost. Looking at your query carefully, it doesn't seem that you ever select from Fruits so you should be able to replace the Null check on the ID with a Where Exists clause and get rid of the Full Outer Join altogether. Something like...

WHERE EXISTS(
        SELECT FRUITS.ID
        FROM FRUITS
        WHERE Fruits.CategoryID = FruitCategories.ID
)