Translate

Monday, February 10, 2014

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)


No comments:

Post a Comment

Comments will appear once they have been approved by the moderator