SQL Declaring Variables in a View

Question

I have created a view which uses dynamic variables in the where statement below.

DECLARE 
@wkFileYear SMALLINT = (select fs.FileYear from dbo.FileSemesters fs 
 where fs.systemcurrentflag = 1),
@wkFileSemester SMALLINT = (select fs.FileSemester from dbo.FileSemesters fs where fs.systemcurrentflag = 1)

Select R.FileType, R.FileYear, R.FileSemester, R.ID, R.ClassCode, R1.Raw
from
(
SELECT SC.FileType, SC.FileYear, SC.FileSemester, SC.ID, SC.ClassCode
FROM StudentClasses AS SC 
) as R
LEFT JOIN
(
SELECT  SAR.Filetype,SAR.FileYear, SAR.FileSemester, SAR.ID, SAR.ClassCode, SAR.Result as Raw
FROM    
StudentAssessmentResults AS SAR
) as R1 

on r.FileYear = R1.Fileyear     
and R.FileSemester = R1.FileSemester
and R.FileType = R1.FileType
and R.ClassCode = R1. ClassCode
and R.ID = R1.ID        

where 
R.FileType = 'A'
AND (R.FileYear = @wkFileYear) 
AND (R.FileSemester = 
case 
when (left(R.classcode,2) = '12' or left(R.classcode,2) = '11') and @wkFileSemester = 4 
then 3
else @wkFileSemester
end
)   

I would like to save this as a view in the database but you can't declare variables inside a view. I have looked at all the info on using CTEs or Table Valued Functions but am not sure at all of the syntax. I have tried creating the CTEs and Table Valued Functions using examples from various sources but no luck.

Was hoping that someone could explain how I could still use the variables but allow me to save this as a view in the database.

Answer 1

It does not make sense for a view's structure to be dependent on variables.

Instead you need to make @wkFileYear and @wkFileSemester columns in your view that you can query against.

Alternatively you could create a db function / stored procedure which returns the data you need, if you want to use variables.

Here's an example of what the stored proc might look like.

        CREATE PROCEDURE [dbo].[sp_GetTestData]
            --Param
            @wkFileYear SMALLINT,
            @wkFileSemester SMALLINT
            AS
        BEGIN

        @wkFileYear = select fs.FileYear from dbo.FileSemesters fs where fs.systemcurrentflag = 1
        @wkFileSemester  = select fs.FileSemester from dbo.FileSemesters fs where fs.systemcurrentflag = 1

        Select R.FileType, R.FileYear, R.FileSemester, R.ID, R.ClassCode, R1.Raw
        from
        (
        SELECT SC.FileType, SC.FileYear, SC.FileSemester, SC.ID, SC.ClassCode
        FROM StudentClasses AS SC 
        ) as R
        LEFT JOIN
        (
        SELECT  SAR.Filetype,SAR.FileYear, SAR.FileSemester, SAR.ID, SAR.ClassCode, SAR.Result as Raw
        FROM    
        StudentAssessmentResults AS SAR
        ) as R1 
        on r.FileYear = R1.Fileyear     
        and R.FileSemester = R1.FileSemester
        and R.FileType = R1.FileType
        and R.ClassCode = R1. ClassCode
        and R.ID = R1.ID where 
        R.FileType = 'A'
        AND (R.FileYear = @wkFileYear) 
        AND (R.FileSemester = 
        case 
        when (left(R.classcode,2) = '12' or left(R.classcode,2) = '11') and @wkFileSemester = 4 
        then 3
        else @wkFileSemester
        end
        )
        end