Dynamic safe statement

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

DECLARE @ExecStr    NVARCHAR (4000),
                  @MemberNo    INT = 64567;

SELECT @ExecStr =
    N'SELECT [mbr].* 
    FROM [dbo].[member] AS [mbr] 
    WHERE [mbr].[member_no] = ' 
        + CONVERT (NVARCHAR (10), @MemberNo); 
EXEC (@ExecStr);
GO

Every time we run the batch with different values the sql engine will decide
The type of the value. This will generate different statements in the cache.

DECLARE @ExecStr    NVARCHAR (4000),
                  @MemberNo    INT = 64567;

SELECT @ExecStr = 
    N'SELECT [m].* 
    FROM [dbo].[member] AS [m] 
    WHERE [m].[member_no] = CONVERT (INT, ' 
        + CONVERT (NVARCHAR (10), @MemberNo) + N')';
EXEC (@ExecStr);
GO

EXEC [QuickCheckOnCache] '%member_no%';
GO

But, when we explicit convert back to column type we generate only one statement in cache.