SQL Server Top statement parameterization method

Declare @ TopCount int
Set @ TopCount = 100
Select top (@ TopCount) * from AdventureWorks. HumanResources. Employee

If you have Like and other clauses, you must use sp_executesql to execute them. The example is as follows:

Declare @ TopCount int -- defines the top quantity
Set @ TopCount = 100
Declare @ Title nvarchar (100) -- Define like content
Set @ Title = '% n %'
Declare @ SelectSql nvarchar (max)
Set @ SelectSql ='
Select top (@ TopCountPar )*
From AdventureWorks. HumanResources. Employee
Where Title like @ TitlePar '-- use parameterized top and like

-- Execution with sp_executesql can improve efficiency
Exec sp_executesql @ SelectSql,
N' @ TopCountPar as int, @ TitlePar as nvarchar (100 )',
@ TopCountPar = @ TopCount, @ TitlePar = @ Title