Thanks Ken and Arif

I don't need to create indexes on the #tempTable so I will impliment the use of a table instead of a temporary table

Definitely I will update the @returnVal = Null to IS NULL as well as creating the tables at the begining of the script to avoid mixing DDL and DML

I will try and get rid of the dynamic SQL. I used it to avoid having the same query repeated with only the table name being changed. I have never used functions in SQL how do they work?

Thanks for the book suggestion I will check it out