In a stored procedure, it it better to simply query data or to construct a query and then execute it? why?

Question

I have worked on SQL stored procedures and I have noticed that many people use two different approaches -

First, to use select queries i.e. something like

Select * from TableA where colA = 10 order by colA

Second, is to do the same by constructing a query i.e. like

Declare @sqlstring varchar(100)
Declare @sqlwhereclause varchar(100)
Declare @sqlorderby varchar(100)

Set @sqlstring = 'Select * from TableA '
Set @sqlwhereclause = 'where colA = 10 '
Set @sqlorderby = 'order by colA'

Set @sqlstring = @sqlstring + @sqlwhereclause + @sqlorderby 
exec @sqlstring

Now, I know both work fine. But, the second method I mentioned is a little annoying to maintain.

I want to know which one is better? Is there any specific reason one would resort to one method over the other? Any benefits of one method over other?

Answer 1

Use the first one. This will allow a query plan to be cached properly, apart from being the way you are supposed to work with SQL.

The second one is open to SQL Injection attacks, apart from the other issues.

With the dynamic SQL you will not get compile time checking, so it may fail only when invoked (the sooner you know about incorrect syntax, the better).

And, you noted yourself, the maintenance burden is also higher.

Answer 2

The second method has the obvious drawback of not being syntax checked at compile time. It does however allow a dynamic order by clause, which the first does not. I recommend that you always use the first example unless you have a very good reason to make the query dynamic. And, as @Oded has already pointed out, be sure to guard yourself against sql injection if you do go for the second approach.

Answer 3

I don't have a full comprehensive answer for you, but I can tell you right now that the latter method is much more difficult to work with when importing the stored procedure as a function in an ORM. Since the SQL is constructed dynamically, you have to manually create any type-classes that are returned from the stored procedure that aren't directly correlated to entities in your model.

With that in mind, there are times where you simply can't avoid constructing a SQL statement, especially when where clauses and joins depend on the parameters passed in. In my experience, I have found that stored procs that are creating large, variably joined/whered statements for EXECs are trying to do too many things. In these situations, I would recommend you keep the Single Responsibility Principle in mind.

Answer 4

Executing dynamic SQL inside a stored procedure reduces the value of using stored procedures to just a saved query container. Stored procedures are mostly beneficial in that the query execution plan (a very costly operation) is compiled and stored in memory the first time the procedure is executed. This means that every subsequent execution of the procedure is bypassing the query plan calculations, and jumping right to the data retrieval portiion of the operation.

Also, allowing a stored procedure to take an executable query string as a parameter is dangerous. Anyone with execute permission on granted on the procedure could potentially cause havoc on the rest of the database.