---

How to use SQL parameters in dynamic queries

In all the situations in which it's impossible to use a direct query, we can use the EXEC statement with a dynamically built string
It's possible, in certain cases, to use the system sp_executesql stored procedure instead, which allows to compile the dynamic query increasing performance during execution

In the example below we'll use a dynamically LIKE clause as a filters

Table Anagrafiche

IDAnag	FirstName	Surname	age
1	Francesco	Sartor	33
2	Simone	Casara	34
4	Alessandro	Chiappin	30
6	Alessio	Trevisan	31

The simpler (but slower) way to filter with a dynamic list of IDs is:

DECLARE @Filter as varchar(100) SET @Filter = 'Ale%' EXEC ('SELECT FirstName, Surname FROM Anagrafiche WHERE FirstName LIKE ''' + @Filter + '''')

And now we increase performance using parameters

DECLARE @StrSql as nvarchar(4000) DECLARE @Filter as nvarchar(100) SET @StrSql = N'SELECT FirstName, Surname FROM Anagrafiche WHERE FirstName LIKE @Filter ' SET @Filter = N'Ale%' EXEC sp_executesql @StrSql, N'@Filter varchar(100)', @Filter = @Filter

The sp_executesql SP needs every string to be in unicode format (nvarchar);
The declaration of parameters with the same names inside and outsite sp_executesql (i.e. @Lista) is not compulsory.
Queries written in this way are compiled and not intepreted by the SQL parser
Eventually varchar types don't need quotation marks

Moreover with SQL parameters it is possible to retrieve OUTPUT values from a query. A typical example is when a table name is not known

DECLARE @StrSql as nvarchar(4000) DECLARE @TableName as varchar(50) DECLARE @RecNum as int SET @TableName = 'Anagrafiche' SET @StrSql = N'SELECT @RecNum = count(*) FROM ' + @TableName + ' WHERE FirstName LIKE ''Ale%'' ' EXEC sp_executesql @StrSql, N'@RecNum int OUTPUT', @RecNum = @RecNum OUTPUT