SQL Stored Procedures - Cost:Benefit When Using Dymanic SQL as SP?

Question

I'm genuinely curious and not the world's most well-versed in the inner workings of SQL. I apologize in advance if my terminology reflects my ignorance.

I've always been under the impression that using Stored Procedures in SQL allows for more efficient operation because the interpreter can predict the nature of the transaction and optimize it before it is called.

My question has to do with using dynamic table names in Stored Procedures, such as

  USE [EODData]
  GO
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  CREATE PROCEDURE [dbo].[Import_Correlation_CSV] 
@fileURL varchar(500)
  AS
  BEGIN
SET NOCOUNT ON;
    Declare @myTableSQL nvarchar(max);
    SET @myTableSQL = 'BULK INSERT EOD_Stock_Correlations FROM '+@fileURL+' WITH ( FIELDTERMINATOR = '','',ROWTERMINATOR = ''\n'')'
    exec sp_executesql @myTableSQL
    end
    GO

or

  --Just the 'meat' this time:
  Declare @ProductsSQL nvarchar(max);
  SET @ProductsSQL = 'update ' + @table + ' set stats_completed = 0'
  exec sp_executesql @ProductsSQL

Note: In each of the above references, @fileURL and/or @table are varchars used as input to the SP

It seems to me that, by use of a "dynamic table," the coder would lose the benefit of Stored Procedure as the SQL engine couldn't pre-interpret or predict the transaction. My own limited testing has shown nominal if any improvement in performance using this method over executing the SQL as a command rather than a SP.

Question(s) What are the specific benefits of using these "unpredictable" Stored Procedures instead of calling for execution of the SQL as a command (C#:NonQuery())? Do any benefits remain when calling a stored procedure that uses dynamic SQL? Essentially - is there any difference between calling a SP in this manner and using NonQuery()/Standard SQL command?

Mind you, I'm not necessarily asking which one is "better," or seeking opinions. I'm asking anyone who knows the inner workings of SQL what the specific advantages/disadvantages may be.

Thank you very much for your time and in advance for any insight or information you can provide.

Answer 1

Well, first and foremost the examples you posted are SQL Injection vectors, both of them. When building dynamic SQL special care needs to be taken to properly handle input:

 SET @myTableSQL = 'BULK INSERT EOD_Stock_Correlations FROM '+ QUOTENAME(@fileURL, '''') +...

and

 SET @ProductsSQL = 'update ' + QUOTENAME(@table) + ' set stats_completed = 0'

Next, I urge you to read The Curse and Blessings of Dynamic SQL and Dynamic Search Conditions in T‑SQL.

Do any benefits remain when calling a stored procedure that uses dynamic SQL?

Some cases these are unavoidable, and sometimes there are significant performance benefits, specially in dynamic search conditions. For the exmaples you posted, in BULK INSERT statement syntax the file must be a literal, it cannot be a parameter. Therefore dynamic SQL is required. Whether this occurs on the client (ie. when you create the SqlCommand text) or on the server (like in the example) is hard to draw a line which is better. If the example you posted would have been correct (ie. use proper QUOTENAME) then I would say 'look, a careful DBA make sure those inexperienced devs don't shoot themselves in the foot and introduce SQL injection in the client code'. As is, the SQL injection exists in the SP so really it adds not much value.

The second example (UPDATE... @table) I consider it a counter example, a code smell of bad practices. It shows that the application schema is potentially unknown and someone simply added too many layers of abstraction and too little value. Again, the SQL injection potential is clear and plainly ignored.

In conclusion: read those two links. Whenever you deal with dynamic, always consider SQL injection risks. Unless explicitly handled, dynamic SQL (both client side or server side) is almost always prone to SQL injection (as your two examples both show it).

Answer 2

Generally, dynamic sql is the same sql but executed in a separate thread. It incurs quite negligible overhead. In most cases you'll notice no perfomance difference. You may wish to read http://www.sommarskog.se/dynamic_sql.html for more details.

Answer 3

First of all, this statement "using Stored Procedures in SQL allows for more efficient operation because the interpreter can predict the nature of the transaction and optimize it before it is called" is not true. Optimizer optimizes batches of statements in the same way, stored proc or not. There are differences between parameterized vs non-parameterized queries, of course, but it's something else.

True reasons for using stored procedures are security and maintainability (ease of refactoring, that is).

Furthermore, dynamic code inside a stored procedure is optimized separately and executed in another context, so there's little difference between this and constructing client-side T-SQL.

And I have to add that using variables for tables is a big code smell (unless it's some administrative task in question).