It seems to me that there are several different arguments going on here and one that has nothing to do with Stored procedures is:

Should there be SQL mixed with perl

This question has nothing to do with the stored procedure question. If you are using code like

my $sql = "EXEC dynamic_example @firstname = ?";
$dbh->execute('chris%')
[download]

to get SQL out of your perl code, then I whould suggest that you should use code like

$dbp->execute('dynamic_example', firstname => 'chris');
[download]

where $dbp holds an object that can either use ad-hoc SQL or stored procedures. The advantages to this approach are that interface to the database is a single object and all the procedures or sql that is accessing the database are well known. The main disadvantage is that if the database needs to accesed in a new way the $dpb object must be extended. And if Stored procedures are used, then the database has to be extended as well.