Reasons not to embed SQL in SSRS reports (.rdl) and create stored procedures instead:
- Easier to version control in SCC
- Better visibility in the database
- Easier to debug
- Easier to fix any performance problems (and to hot fix, if necessary)
- Easier to re-use functionality (which is often lengthy to produce) with other systems/APIs etc
- Stored Procedures get given a name that accurately describes what they do
- Easier to gather execution statistics
- Easy to see what parameters are used to filter
- Can be secured with explicit permissions
- Easier to write automated testing against a stored procedure
All seem fairly obvious, but it’s surprising how many people still embed SQL into SSRS reports.