RSA Scripts

sp_executesql unsafe statement

  1. Sql Server
  2. Cache
  3. Intermediate
  4. Vaknin Sammy

Last updated:

Warning: Review and test in a non-production environment before running.

Back to results

-- Adapted from demo scripts by Kimberly L. Tripp, SQLskills – https://www.sqlskills.com/blogs/kimberly/

DECLARE @ExecStr    NVARCHAR (4000);
SELECT @ExecStr =
    N'SELECT [m].* 
    FROM [dbo].[member] AS [m] 
    WHERE [m].[lastname] LIKE @lastname';
EXEC [sp_executesql] @ExecStr
    , N'@lastname VARCHAR (15)'
    , 'Tripp';
GO
                      
DECLARE @ExecStr    NVARCHAR (4000);
SELECT @ExecStr =
    N'SELECT [m].* 
    FROM [dbo].[member] AS [m] 
    WHERE [m].[lastname] LIKE @lastname';
EXEC [sp_executesql] @ExecStr
    , N'@lastname VARCHAR (15)'
    , 'Anderson';
GO

DECLARE @ExecStr    NVARCHAR (4000);
SELECT @ExecStr = 
    N'SELECT [m].* 
    FROM [dbo].[member] AS [m] 
    WHERE [m].[lastname] LIKE @lastname';
EXEC [sp_executesql] @ExecStr
    , N'@lastname VARCHAR (15)'
    , '%e%';
GO

EXEC [QuickCheckOnCache] '%member%';
GO

 

After the first statement is executed we force the plan to be inserted into cache.
But, if the next execute statement return many more rows and we use the same plan
we will get wrong values in the execution plan.

look at the execution plan for the second and therd statements to see the different
between estimated rows and actual rows.