title: how to create view with variables inside?
body: <p>i have some code here but first i want to explane my question.</p>
  
  <p>i want to create a view but thats not possible in a view because my <code>SELECT</code> statement hase some <code>variables</code> in it i have seen some answers with <code>function</code> but i dont know how the parameters works.</p>
  
  <p>here is my code </p>
  
  <p>CODE:</p>
  
  <pre><code>DECLARE @totalRecords INT
  DECLARE @I INT
  
  SELECT @I = 1
  
  SELECT @totalRecords = COUNT([Id]) FROM [dbo].[Werkzaamheden]
  
  WHILE (@I &lt;= @totalRecords)
  
  BEGIN
  
  SELECT 
      U.[Id]
      , G.[Naam]
      , W.[Omschrijving]
      , U.[datum]
      , CONVERT(VARCHAR,100)+'%' AS 'Status'
      , W.[Norm]
      , W.Id
  
  FROM [Werkzaamheden] W
          RIGHT JOIN [Uitgevoerd] U ON U.[Taak] = W.[Id] /*or left */
          FULL JOIN [Gebruikers] G ON G.[Naam] = U.[Naam]
  
  WHERE U.[Status]= 1 AND W.Id = @I
  
  UNION 
  
  SELECT 
      U.[Id]
      , G.[Naam]
      , W.[Omschrijving]
      , U.[datum]
      , CONVERT(VARCHAR,0)+'%' AS 'Status'
      , W.[Norm]
      , W.Id
  FROM [Werkzaamheden] W
          LEFT JOIN [Uitgevoerd] U ON U.[Taak] = W.[Id] /*or left */
          FULL JOIN [Gebruikers] G ON G.[Naam] = U.[Naam]
  
  WHERE U.[Status]= 0 AND W.Id = @I
  
  UNION
  
  SELECT 
      U.[Id]
      , G.[Naam]
      , W.[Omschrijving]
      , U.[datum]
      , null AS 'Status'
      , '' AS Norm
      , W.Id
  FROM [Werkzaamheden] W
          RIGHT JOIN [Uitgevoerd] U ON U.[Taak] = W.[Id] /*or left */
          FULL JOIN [Gebruikers] G ON G.[Naam] = U.[Naam]
  
  WHERE U.[Status]IS NULL 
  
      SELECT @I = @I + 1
  
  END
  </code></pre>
  
  <p>what is the best salution ??</p>
  
  <p>this is what it return:</p>
  
  <pre><code>Id  Naam    Omschrijving    datum   Status  Norm    Id
  NULL    Johan   NULL    NULL    NULL    0   NULL
  8   Piet    Luisteren   2013-05-14  0%  95  3
  9   Klaas   Luisteren   2013-05-16  100%    95  3
  13  Klaas   Luisteren   2013-05-17  0%  95  3
  14  Janneke Luisteren   2013-05-17  100%    95  3
  </code></pre>
  
  <hr>
  
  <pre><code>Id  Naam    Omschrijving    datum   Status  Norm    Id
  NULL    Johan   NULL    NULL    NULL    0   NULL
  4   Janneke Schoonmaken 2013-05-14  100%    20  2
  5   Jan Schoonmaken 2013-05-14  0%  20  2
  10  Piet    Schoonmaken 2013-05-16  100%    20  2
  11  Janneke Schoonmaken 2013-05-16  100%    20  2
  12  Piet    Schoonmaken 2013-05-17  100%    20  2
  </code></pre>
  
  <p>and that is good</p>