CODEWITHSUNDEEP
sqlsql-servert-sql
Anil Namde
Anil Namde

Reputation: 6618

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

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 ) ?

Upvotes: 5

Views: 512

Answers (4)

Nicholas Carey
Nicholas Carey

Reputation: 74345

Depends on what you deem the 'purpose' of stored procedures to be. I'd argue that stored procedures exist to expose a securable API to the database and isolate the application(s) from the database implementation, by keeping SQL out of the codebase.

Doing so makes the database a separate component that can be modified and versioned independently from the application code referencing it (so long at the API signature doesn't change).

If all your database access is via stored procedures, so long at the stored procedure arguments and results sets don't change, the DBAs are free to tune queries and remodel the db to their hearts content, so long as they don't break assumptions the code makes about the data model: one can [theoretically] completely change the underlying schema without requiring code changes.

That's the true benefit of stored procedures.

Upvotes: 1

Ash Machine
Ash Machine

Reputation: 9911

Instead of EXEC(@SQL) try sp_execute(@SQL).

sp_execute(@SQL) promotes query plan reuse. When using sp_executesql, a user or an application explicitly identifies the parameters. Read about plan caching issues here at this technet article, see #3.

Upvotes: 1

Cade Roux
Cade Roux

Reputation: 89721

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.

Upvotes: 7

mariocase84
mariocase84

Reputation: 436

it depends on the complexity of the execution plan. For this particular case I see no problem with the stored procedure as the execution plan will be quite simple.

Upvotes: 0

Related Questions