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

Answer 2

You can cheat a little bit by using a CTE to resolve any variables that would like to declare. If the CTE for these settings only has a single row, then we can use a cross join to merge the settings so that they are available to each row in the view.

CREATE VIEW CurrentSemesterClasses 
AS

WITH SystemSettings as (
    -- Query to resolve the Variables, MUST only return a single row
    SELECT TOP 1 FileYear, FileSemester
    FROM dbo.FileSemesters
    WHERE SystemCurrentFlag = 1
)
SELECT R.FileType, R.FileYear, R.FileSemester, R.ID, R.ClassCode, R1.Raw
FROM StudentClasses R, SystemSettings
LEFT JOIN StudentAssessmentResults 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 = SystemSettings.FileSemester
  AND R.FileSemester = CASE 
                            WHEN (left(R.classcode,2) = '12' or left(R.classcode,2) = '11') and SystemSettings.FileSemester = 4 then 3
                            ELSE SystemSettings.FileSemester
                       END

NOTE: I have simplified your original query by referencing the required tables directly rather than using the strange sub-query that was used. This syntax should behave the same or have better performance than the original, but it ultimately much easier to maintain.

There is no performance benefit to sub-querying just the columns that you think you need via sub-queries instead of joining directly onto tables. This syntax looks like you are expecting the sub-query to cache a subset of the data table but this is not how it works. Do not make the mistake of assuming the sub-query is resolved entirely first before the main query

In this particular case, if by convention or application logic there will only ever be a single row in the dbo.FileSemesters table that has SystemCurrentFlag = 1 then we can further simplify this query to a simple join:

CREATE VIEW CurrentSemesterClassesSimple 
AS
SELECT R.FileType, R.FileYear, R.FileSemester, R.ID, R.ClassCode, R1.Raw
FROM StudentClasses R
INNER JOIN dbo.FileSemesters S ON R.FileYear = s.FileYear AND S.SystemCurrentFlag = 1
LEFT JOIN StudentAssessmentResults 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.FileSemester = CASE 
                            WHEN (left(R.classcode,2) = '12' or left(R.classcode,2) = '11') and S.FileSemester = 4 then 3
                            ELSE S.FileSemester
                       END

To complete the set, the following is how you would package the cleaned up original query into a TVF - Table Valued Function

CREATE FUNCTION CurrentSemesterClassesFn
(
    -- This function has no parameters
)
RETURNS
@Table_Var TABLE
(
    -- Please update the types of these columns to match your schema!
      FileType CHAR(1)
    , FileYear INT
    , FileSemester INT
    , ID INT
    , ClassCode VARCHAR(100)
    , [Raw] VARCHAR(max)
)
CREATE VIEW CurrentSemesterClassesSimple 
AS
BEGIN

DECLARE @wkFileYear SMALLINT, @wkFileSemester SMALLINT;
SELECT @wkFileYear = fs.FileYear
     , @wkFileSemester = fs.FileSemester
FROM dbo.FileSemesters fs 
WHERE fs.systemcurrentflag = 1;

INSERT INTO @Table_Var (FileType, FileYear, FileSemester, ID, ClassCode, Raw)
SELECT R.FileType, R.FileYear, R.FileSemester, R.ID, R.ClassCode, R1.Raw
FROM StudentClasses R
LEFT JOIN StudentAssessmentResults 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 S.FileSemester
                       END

    RETURN
END