how to create view with variables inside?

Question

i have some code here but first i want to explane my question.

i want to create a view but thats not possible in a view because my SELECT statement hase some variables in it i have seen some answers with function but i dont know how the parameters works.

here is my code

CODE:

DECLARE @totalRecords INT
DECLARE @I INT

SELECT @I = 1

SELECT @totalRecords = COUNT([Id]) FROM [dbo].[Werkzaamheden]

WHILE (@I <= @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

what is the best salution ??

this is what it return:

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

---

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

and that is good

Answer 1

Why not this? No loop and no UNION?

SELECT 
    U.[Id]
    , G.[Naam]
    , W.[Omschrijving]
    , U.[datum]
    , CASE
          WHEN U.Status = 1 THEN CONVERT(VARCHAR,100)+'%' 
          WHEN U.Status = 0 THEN CONVERT(VARCHAR,100)+'%' 
          ELSE NULL
      END 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 = 0 OR U.Status = 1 OR U.Status IS NULL