Yes, there tons of benefits to stored procedures. From the SQL SERVER perspective.... This all depends on the query complexity and the architecture of the data. If you have no control of the architecture of the data then Stored procedures can still allow you to select chunks of the data out of the database into a temp tables. Index it, reuse it, perform complex set based calculations on it, join it etc. Tableau does add a significant about of overhead to stored procedures. To get around it try this approach: Stored Procedures In SQL Server Without all the Tableau Overhead
Main benefits would be conditional and procedural logic e.g. ability to write loops, run multiple SQL statements in a sequence and pass the results of one to the other or trigger conditional logic. It can also enforce limits on how much data user can see e.g. you can parameterize a stored procedure and require people to pick a StoreID before they can proceed, which means they can only see one Store at a time instead of querying the full dataset which maybe prohibitive due to data size etc.