scalar variable error after it has been declared

Viewed 31

I'm running Microsoft SQL Server 17.7 and I keep getting an error message when trying to filter my data set by a date range with a where clause using a scalar variable. I declared both variables but I continue to get this error message "Must declare the scalar variable "@startDate". I'm guessing I'm not declaring the scalar variable correctly. Please assist.

DECLARE @MatterList NVARCHAR(100) = '(21940-00035)'
declare @startDate datetime = '1/1/2012'
declare @endDate datetime = '6/30/2015'

SELECT @MatterList = NULL 
WHERE @MatterList = ''

DROP TABLE IF EXISTS #listMatter
DROP TABLE IF EXISTS #Matterlist

CREATE TABLE #MatterList (rowid int identity, MatterNumber nvarchar(20))
CREATE INDEX tMatterListIndex_MatterNumber on #MatterList (MatterNumber)

select value into #listMatter from 
string_split(replace(replace(replace(@MatterList,'(',''),')',''), ' ' , ''), ',')


Insert into #MatterList (MatterNumber)
select m.Number as MatterNumber
from #listMatter lm
inner join Matter m with (nolock) on lm.value = m.Number
where m.Number not in (select matterNumber from #MatterList)

If OBJECT_ID('tempdb..#fees_detail') is not NULL drop table #fees_detail
GO

SELECT DISTINCT 'Fees' AS [type],
            t.code AS tudef2,
            tc.PostDate AS bfindt,
            tc.IsActive AS isActive,
            tc.IsNoCharge AS isNoCharge,
            m.OpenDate AS mopendt,
            m.CloseDate AS mclosedt,
            m.ContactInfo AS 'matter_contact',
            a.DESCRIPTION AS 'matter_arrangement',
            m.BillingInstruc AS 'billing_instructions',
            m.Comments AS 'terms',
            m.Number AS 'matter_id',
            c.DisplayName AS 'client_name',
            m.DisplayName AS 'matt_desc',
            m.Narrative_UnformattedText AS 'matt_nar',
            btk.Number AS 'batty_id',
            btk.DisplayName AS 'batty_name',
            tk.Number AS 'tk_id',
            tk.DisplayName AS 'tk_name',
            td.HireDate AS 'tkemdate',
            title.DESCRIPTION AS 'tktitle',
            htitle.DESCRIPTION AS 'htitle',
            CASE htitle.DESCRIPTION 
                WHEN 'partner' 
                    THEN 1 
                WHEN 'associate' 
                    THEN 2
                WHEN 'summer assoc.' 
                    THEN 3 
                WHEN 'staff counsel' 
                    THEN 4 
                WHEN 'paralegal' 
                    THEN 5
                WHEN 'als' 
                    THEN 6 
                WHEN 'CSS admin' 
                    THEN 7 
                WHEN 'legal graphics' 
                    THEN 8
                WHEN 'library' 
                    THEN 9 
                WHEN 'case clerks' 
                    THEN 10
                WHEN 'lit analyst' 
                    THEN 11 
                        ELSE 12 
            END AS 'tktitle_sort',
            d.DESCRIPTION AS 'mdept',
            pg.DESCRIPTION AS 'mprac',
            o.DESCRIPTION AS 'mloc',
            tc.TimeIndex AS 't_index',
            tc.WorkDate AS 'worked_date',
            CONVERT(VARCHAR(6),tc.workdate,112) AS 'worked_period',
            tc.WorkHrs AS 'tworkhrs',
            tc.WorkRate AS 'tworkrt',
            tc.WorkAmt AS 'tworkdol',
            tc.WIPRemoveDate AS 'tbilldt',
            tc.WIPHrs AS 'tbillhrs',
            tc.WIPRate AS 'tbillrt',
            tc.WIPAmt AS 'tbilldol',
            tc.Narrative_UnformattedText AS 'time_desc',
            '' AS 'ccode',
            '' AS 'codesc1',
            CASE 
                WHEN tc.IsNB = 1 
                    THEN 0.00 
                        ELSE  tc.WIPAmt - tc.WorkAmt 
            END AS 'adj'
INTO  #fees_detail
FROM timecard tc WITH (NOLOCK)
            INNER JOIN matter AS m WITH (NOLOCK) 
            ON tc.matter = m.mattindex
            INNER JOIN #MatterList AS tm 
            ON m.Number = tm.MatterNumber
            INNER JOIN mattdate AS md WITH (NOLOCK) 
            ON m.MattIndex = md.MatterLkUp 
                AND CONVERT(DATE,GETDATE()) BETWEEN  md.NxStartDate AND md.NxEndDate
            INNER JOIN client AS c WITH (NOLOCK) 
            ON m.client = c.ClientIndex
            INNER JOIN PracticeGroup AS pg WITH (NOLOCK) 
            ON md.PracticeGroup = pg.Code
            INNER JOIN Department AS d WITH (NOLOCK) 
            ON md.Department = d.Code
            INNER JOIN Arrangement AS a WITH (NOLOCK) 
            ON md.Arrangement = a.Code
            INNER JOIN Office AS o WITH (NOLOCK) 
            ON md.Office = o.Code
            INNER JOIN Timekeeper AS tk WITH (NOLOCK) 
            ON tc.Timekeeper = tk.TkprIndex
            INNER JOIN TkprDate AS td WITH (NOLOCK) 
            ON tk.TkprIndex = td.TimekeeperLkUp 
                AND CONVERT(DATE,GETDATE()) BETWEEN td.NxStartDate AND td.NxEndDate
            INNER JOIN TkprDate AS htd WITH (NOLOCK) 
            ON tk.TkprIndex = htd.TimekeeperLkUp 
                AND tc.WorkDate BETWEEN htd.NxStartDate AND htd.NxEndDate
            INNER JOIN Timekeeper AS btk WITH (NOLOCK) 
            ON md.BillTkpr = btk.TkprIndex
            INNER JOIN title WITH (NOLOCK) 
            ON td.title = title.code
            INNER JOIN title AS htitle WITH (NOLOCK) 
            ON htd.title = htitle.code
            LEFT OUTER JOIN task AS t WITH (NOLOCK) 
            ON tc.Task = t.TaskID

Where tc.WorkDate between @startDate and @enddate
ORDER BY m.Number, tc.WorkDate, tc.TimeIndex

SELECT  
[type], tudef2, bfindt, isActive, isNoCharge, mopendt, mclosedt, matter_contact, 
matter_arrangement, billing_instructions, terms, matter_id, client_name, matt_desc, matt_nar, 
batty_id, batty_name, tk_id, tk_name, tkemdate, tktitle, htitle, tktitle_sort, mdept, mprac, 
mloc, t_index, worked_date, worked_period, tworkhrs, tworkrt, tworkdol, tbilldt, tbillhrs, 
tbillrt, tbilldol, time_desc, ccode, codesc1, adj
FROM #fees_detail   

ORDER BY 29
1 Answers

GO flags the end of a batch of statements, i.e. everything from the beginning of the file (or the previous GO) is sent as one batch, and executed; this was probably ok, then everything following GO until the end of the file or the next GO is sent and executed, when this happened your declarations were already gone (not part of this batch. Remove GO.

Related