Pros and Cons of using Dynamic SQL vs Stored procedure

[Zirak]

Hi,
I'm designing a new application using a sql server backend. The application will basically integrate data from different departments.
I am investing some time to design one main form where users can view data about every thing. Basiclly with a tree view on the left listing all departmetns and objects they need.
I want to build a generic search module that I will be able to use for all objects (tables).
For example, consider a table (tblPerson), you can search a table either by a Descriptive field (PersonName) a Foriegn Key (CompanyID,AddressID) or a PrimaryKey (PersonID).
I am thinking of building a stored procedure that will accept the field you are searching and the table name and then dynamicall build the sql statment.

The pros is obvious : Cleaner Code, better maintenance, REUSE and efficiency


The Cons:
I have to manage the permission on a table level vs Stored procedure level

and my main concern is that : i don't know how it will effect the performance if I use sql statments vs stored procedures (regarding the execution plans and the fact that stored procedures sit in memory)?

Any ideas?


Thanks

 

[donutman]

Before I'd write an application like that, I would look for a commercial app. There's bound to be one.
-Karl

[red] Cursors, Triggers and User Definded Functions are part of the Axis of Evil![/red]

 

[ESquared]

For what it's worth, there is an alternative to managing security at the table level.

You can create a view, owned by the same owner as all your tables, and give permission to just the view.

This way, many tables can be accessed by the view, but you only have to give permission to one object.

As for execution plans, look up the help and search the internet for sp_executesql. Using this instead of "Exec @SQLString" can improve performance because execution plans can be reused.

I personally think it's best to accept parameters into a stored procedure and build the SQL dynamically in there. That keeps the 'brains' inside the database and makes you less dependent on the client-side stuff.

 

[ESquared]

Oh... did you know you can return multiple rowsets from a stored procedure?

You could build a front-end application that gets an entire screen of data in one operation from a stored procedure using this method. Efficient!

 

[RileyCat]

The biggest con to using Dynamic SQL is it's not optimized. The programmer cannot pre-optimize the SQL code by using rules, tips, and other query optimization tricks. Dynamic SQL is difficult to debug. It may be flexible but you cannot always capitalize on index tables or do any pre-optimization that SQL Server offers for stored procedures.