Does dynamically formed SQL in Stored Procedures negate the very purpose of Stored Procedures?

Question

We have old project 10-12 years now. It was using SQL2000 which we have now moved to SQL2008.

While this task i found that Stored Procedures were accepting parameters and then constructing the query as a string and then using EXEC to execute the command.

CREATE PROCEDURE MyProc
  (@TableName varchar(255),
   @FirstName varchar(50),
   @LastName varchar(50))
AS

    -- Create a variable @SQLStatement
    DECLARE @SQLStatement varchar(255)

    -- Enter the dynamic SQL statement into the
    -- variable @SQLStatement
    SELECT @SQLStatement = "SELECT * FROM " +
                   @TableName + "WHERE FirstName = '"
                   + @FirstName + "' AND LastName = '"
                   + @LastName + "'"

    -- Execute the SQL statement
    EXEC(@SQLStatement)

Is this a bad approach. Does this kills benefits of Stored Procedure (pre-compiled query benefit ) ?

Answer 1

No, and I would argue that the main benefit of stored procedures is no longer the fact that it is "pre-compiled" (since 2005 or earlier, perhaps never, except for very high volume calls).

There is a plan cache which is also available for ad hoc statements.

This particular example has reintroduced a vulnerability to injection which would be automatic with:

CREATE PROCEDURE MyProc
  (@FirstName varchar(50),
   @LastName varchar(50))
AS
BEGIN
    SELECT * FROM TABLENAME
    WHERE FirstName = @FirstName
        AND LastName = @LastName
END

All for the sake of being parameterized on table name.

Benefits of stored procedures do include:

• Security Management (being able to control EXEC rights independently of SELECT/INSERT/UPDATE)

• Access Coordination (ensuring all operations are done in certain ways)

• Organization (being able to organize the interface to the database in a coherent way)

• Injection Prevention (stored procedures are always parameterized, this ensures callers are not able to make database cases which are vulnerable to injection - note that the SPs themselves need to be properly written, but client programmers will not be able to introduce injections if they only have access to SPs and not tables)

• System Inventory (being able to profile and optimize certain procedures by name, being able to have a comprehensive and well-documented interface layer made up of stored procedures)

Dynamic SQL in an SP has its place, and it can negate some things - like the security (it starts a new chain, so SELECT permissions will need to be granted here) and injection. Execution plan caching is not one that I would put high on the list.