title: Must declare the scalar variable error
body: <p>This is in connection with my last question. I have this code:</p>
  
  <pre><code>DECLARE @PatientId UNIQUEIDENTIFIER
  DECLARE @FormId NCHAR(16)
  DECLARE @columns VARCHAR(8000)
  DECLARE @query VARCHAR(8000)
  
  SET @PatientId = '3387F956-EA0A-4660-8647-1096E25A3C6F'
  SET @FormId = 'form111'
  
  SELECT @columns = COALESCE(
          @columns + ',[' + RTRIM(AttributeGroup) + '_' + RTRIM(AttributeId) + ']',
          '[' + RTRIM(AttributeGroup) + '_' + RTRIM(AttributeId) + ']'
      )
  FROM DVItems
  WHERE PatientId = @PatientId AND FormId = @FormId
  GROUP BY AttributeGroup, AttributeId
  
  SET @query = '
  SELECT * 
  FROM( 
      SELECT
          PatientId,
          RTRIM(AttributeGroup) + ''_'' + RTRIM(AttributeId) AS ColName,
          FormId,
          Description
      FROM DVItems
      WHERE PatientId = @PatientId AND FormId = @FormId
  ) p
  PIVOT
  (
      MAX(Description)
      FOR [ColName]
      IN (' + @columns + ')
  ) AS p'
  
  EXECUTE (@query)
  </code></pre>
  
  <p>When I execute this. It says "Must declare the scalar variable "@PatientId"." But when I commented out the lines where I SET value for @PatientId and @FormId and execute it, it says "Command(s) completed successfully." Any help? Thank you. Try this <a href="http://sqlfiddle.com/#!3/3100c/3" rel="nofollow">fiddle</a>.</p>