RE: Need help using a Table variable in a dynamic sql query

ChrisM@Work

SSC Guru

Points: 186127

June 14, 2016 at 2:05 am

#1884828

Qira (6/11/2016)
...
Please help. As a result, I know - How to use Table variable in a dynamic sql query

This batch looks horrendously overcomplicated for what it's doing. Why can't you make the whole lot a single query?

SELECT

@get_amt_Year1 = SUM(CASE WHEN year_num = @year1 THEN close_amt ELSE 0 END),

@get_amt_Year2 = SUM(CASE WHEN year_num = @year2 THEN close_amt ELSE 0 END)

FROM tmpaccthist_2013_2014_report_2

WHERE year_num IN (@year1,@year2)

AND period_num = @period

AND chart_code in (81106,81118,81202)

AND campus_cd IN (SELECT [Value] FROM fn_Split(@campus_cd, ','))

AND kod IN (SELECT [Value] FROM fn_Split(@cmpy_code, ','))

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden