To make your statement work you have to convert @pid in the string concatenation.

SET @SQL = 'delete from ' + @tablename + ' where '+ @pidname + ' = '+ cast( @pid as varchar(50) )

To avoid sql injection you should not use dynamic statements like this at all. Write a separate delete procedure for every table where you want to delete data from.