Does dynamically generated SQL in stored procedures negate the very purpose of stored procedures?

Question

We have an old project of 10-12 years. He used SQL2000, which we have now moved to SQL2008.

During this task, I discovered that stored procedures take parameters, and then build the query as a string, and then use 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)

This is a bad approach. Does this mean the benefits of a stored procedure (pre-compiled query benefit)?

Answer 1

, , - , " " ( 2005 , , , ).

, .

, :

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

.

:

• ( EXEC SELECT/INSERT/UPDATE)

• ( )

• ( )

• ( , , , , - , SP , , SP, )

• System Inventory ( , , )

SQL SP , - , ( , SELECT ) . , .

Answer 2

EXEC (@SQL) sp_execute (@SQL).

sp_execute (@SQL) . sp_executesql . , . # 3.

Answer 3

, "" . , , API () , SQL .

, , ( API ).

, , db , t , : , .

.

Answer 4

. , .