Dynamic SQL with parameters example

--Create some fake data
SELECT *

INTO   #temp

FROM   (SELECT 5         AS number,

               'Fritz'   AS name,

               'Mustang' AS car,

               21        AS age

        UNION

        SELECT 1         AS number,

               'Tom'     AS name,

               'Ferrari' AS car,

               45        AS age

        UNION

        SELECT 2             AS number,

               'Tom'         AS name,

               'Lamborghini' AS car,

               65            AS age)x

--Declare parameters

DECLARE @Query NVARCHAR(max)

DECLARE @ParameterDefinition NVARCHAR(max)

DECLARE @minAge INT

DECLARE @name VARCHAR(max)

--Set parameters

SET @minAge=30

SET @name='Tom'

SET @Query=

'select * from #temp where age>@minAge and name=@name order by age desc'

SET @ParameterDefinition=N'@name varchar(max),@minAge int'


--Print the query for debugging
PRINT @Query

--Execute query

EXECUTE sp_executesql

  @Query,

  @ParameterDefinition,

  @name,  @minAge--parameter values have to be in the same order as in the declaration (@ParameterDefinition)