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.[EmployeeID]
      ,c.[Title]
      ,c.[FirstName]
      ,c.[MiddleName]
      ,c.[LastName]
      ,c.[Suffix]
      ,e.[Title] AS [JobTitle] 
      ,c.[Phone]
      ,c.[EmailAddress]
      ,c.[EmailPromotion]
      ,a.[AddressLine1]
      ,a.[AddressLine2]
      ,a.[City]
      ,sp.[Name] AS [StateProvinceName] 
      ,a.[PostalCode]
      ,cr.[Name] AS [CountryRegionName] 
      ,c.[AdditionalContactInfo]
     FROM 
      [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: