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