It depends, on what you're looking for. Dynamic SQL has the advantage you're flexible and are not depended on what is implemented on the database.

SPs, have the advantage of having greater control of what is allowed on the database, so enables greater security.

As for the performance arguments, in the article passed to you by mike, well, it is hardly relevant for most cases. Parameterized dynamic SQL is just as capable of reusing execution plans as SPS. Also, reusing execution plans is not always desirable as it really depends on the variaty of the the parameter values. I find it hard to imagine that the performance difference are really made by the choice of SPT or SP. In most cases performance is just depended on good database design and constructing the right SQL statements.

Typically I'd use a mixed approach. If I seek for ultimate performance for some cases, I'd go for SPs. For flexibility I'd go for dynamic SQL. If your application is holding very sensitive and secure data, I'd go for SPs as well. If you just are looking for storing and retrieving data in a quick and flexible way, I'd definately would avoid going through the hoops of SPs.

Walter,





>I'm sure this will generate a whole host of responses, but here goes.....
>
>Which is a better approach - doing everything through stored procs, or
>passing Create, Retrieve, Update, and Delete commands to SQL?
>
>Thanks
>
>K