I would like to understand the advantage in sourcing report from stored procedure instead of select queries. Please share your thoughts, it would be of great help for me. Thanks in advance for your inputs. Thanks, Karthik

Karthik, Check this out:- http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/8e174e3f-b342-429c-83bf-9c9d6e35bb70 http://connect.microsoft.com/SQLServer/feedback/details/681130/how-to-by-pass-sp-executesql-without-using-user-defined-stored-procedure Thanks Kumar Please do let us know your feedback. Thank You - KG, MCTS

Thanks for the response Kumar. My concern is different from your links. As of now I have all my reports running with direct SELECT queries that reside in the report RDL file, I wanna replace all these SELECT queries with stored procedure. Before doing that i wanted to understand the adv and disadv in doing that. Thanksm Karthik

I got it guys... http://sqlserverpedia.com/blog/sql-server-bloggers/ssrs-should-i-use-embedded-tsql-or-a-stored-procedure/ http://blog.hoegaerden.be/2009/11/10/reporting-on-data-from-stored-procedures-part-1/

Hi Karthik, These are few obvious reasons why I started using SP's instead of text queries. 1. Resuability 2. You have your SP in a server so if need be just alter your SP. If you design your reports with text query you need to make changes to the dataset which consumes more time than modifying your SP. (I would say this is one good enough reason). 3. Adding to the above, if there is a field change you can alter the SP and still use the initial column name using "AS" thus avoiding reopening the rdl in design view, then altering the query for each report and etc etc. Hope these are convincing enough, Vignesh Viswanathan