Saturday, 17 June 2006

Dynamic SQL - Passing a parameter using sp_execute

Dynamic SQL : Passing a parameter to sp_execute
Example uses AdventureWorks db

exec sp_executesql N'SELECT 
      ,e.[Title] AS [JobTitle] 
      ,sp.[Name] AS [StateProvinceName] 
      ,cr.[Name] AS [CountryRegionName] 
      [HumanResources].[Employee] e
     INNER JOIN [Person].[Contact] c 
       ON c.[ContactID] = e.[ContactID]
     INNER JOIN [HumanResources].[EmployeeAddress] ea 
       ON e.[EmployeeID] = ea.[EmployeeID] 
     INNER JOIN [Person].[Address] a 
       ON ea.[AddressID] = a.[AddressID]
     INNER JOIN [Person].[StateProvince] sp 
       ON sp.[StateProvinceID] = a.[StateProvinceID]
     INNER JOIN [Person].[CountryRegion] cr 
       ON cr.[CountryRegionCode] = sp.[CountryRegionCode]
         AND cr.[Name] = @Region',N'@Region varchar(20)','Germany'

No comments: