SQL - Putting variable to the code

Dear Experts,

I have quite a long query to produce pivot kind of summaries per categories, in the attached example checking tax number so the keyword is "VAT":


-- Country level of pivot for VAT check
-- SELECT * FROM #tCustomerQCResult WHERE #tCustomerQCResult.VATCheck <> 'Ok'

;WITH                   
      PivotVATCheckCorrect AS (             
            SELECT DISTINCT      
                  #tCustomerQCResult.Country AS Country,
                  COUNT(DISTINCT #tCustomerQCResult.Customer) AS CorrectVATCheckPerCC
            FROM      
                  #tCustomerQCResult
            WHERE      
                  #tCustomerQCResult.VATCheck = 'Ok'
            GROUP BY      
                  #tCustomerQCResult.Country
            )      
            ,      
      PivotVATCheckIncorrect AS (             
            SELECT DISTINCT      
                  #tCustomerQCResult.Country AS Country,
                  COUNT(DISTINCT #tCustomerQCResult.Customer) AS IncorrectVATCheckPerCC
            FROM      
                  #tCustomerQCResult
            WHERE      
                  #tCustomerQCResult.VATCheck <> 'Ok'
            GROUP BY      
                  #tCustomerQCResult.Country
            )      
            ,      
      PivotVATCheckIncorrectNew AS (             
            SELECT DISTINCT      
                  #tCustomerQCResult.Country AS Country,
                  COUNT(DISTINCT #tCustomerQCResult.Customer) AS IncorrectVATCheckPerCCNew
            FROM      
                  #tCustomerQCResult
            WHERE      
                  #tCustomerQCResult.NewVATError = 'NewVATError'
            GROUP BY      
                  #tCustomerQCResult.Country
            )      
            ,      
      PivotVATCheckTotal AS (             
            SELECT DISTINCT      
                  #tCustomerQCResult.Country AS Country,
                  COUNT(DISTINCT #tCustomerQCResult.Customer) AS IncorrectVATCheckPerCC
            FROM      
                  #tCustomerQCResult
            GROUP BY      
                  #tCustomerQCResult.Country
            )      
                  
SELECT                  
      #CustomerQCCountries.LAND1 AS Country,            
      ISNULL(PivotVATCheckCorrect.CorrectVATCheckPerCC,0) AS CorrectVAT,            
      ISNULL(PivotVATCheckIncorrect.IncorrectVATCheckPerCC,0) AS IncorrectVAT,            
      ISNULL(PivotVATCheckIncorrectNew.IncorrectVATCheckPerCCNew,0) AS IncorrectVATNew,            
      ISNULL(PivotVATCheckTotal.IncorrectVATCheckPerCC,0) AS GrandTotalVAT,            
      ISNULL(FORMAT(ISNULL(PivotVATCheckCorrect.CorrectVATCheckPerCC,0) * 1.0 / NULLIF(ISNULL(PivotVATCheckTotal.IncorrectVATCheckPerCC,0) * 1.0, 0),'p'),'N/A') AS 'Percent'            
FROM                  
      #CustomerQCCountries            
LEFT JOIN                  
      PivotVATCheckCorrect ON #CustomerQCCountries.LAND1 = PivotVATCheckCorrect.Country            
LEFT JOIN                  
      PivotVATCheckIncorrect ON #CustomerQCCountries.LAND1 = PivotVATCheckIncorrect.Country            
LEFT JOIN                  
      PivotVATCheckIncorrectNew ON #CustomerQCCountries.LAND1 = PivotVATCheckIncorrectNew.Country            
LEFT JOIN                  
      PivotVATCheckTotal ON #CustomerQCCountries.LAND1 = PivotVATCheckTotal.Country            
ORDER BY                  
      #CustomerQCCountries.LAND1            


Next I will have to duplicate this query and replace the "VAT" word with "Direct Debit" which is another category to summarize, actually pasting this code to Excel and replacing "VAT" with "Direct Debit" everywhere and it works.

Could you please advise would it be maybe some method in SQL to put variable to straight into the code? Just an idea but so around the pattern of

DECLARE @ErrorCategory nvarchar(10)
SELECT @ErrorCategory = 'VAT'

and than later changing that 'VAT' to 'Direct Debit' in the beginning of the query and run as a totally replaced code

Thanks in advance,
LVL 1
csehzIT consultantAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase ExpertCommented:
In this scenario we have to use dynamic SQL so that we can build the string and after that execute using EXEC or sp_executesql.

I have made your query dynamic. Please try and let us know in case of any issues.

SELECT @ErrorCategory = 'VAT' /* Just change this variable of @ErrorCategory when you want to run it for difference error category..,*/

DECLARE @ErrorCategory nvarchar(100)
SELECT @ErrorCategory = 'VAT'

DECLARE @SQL AS VARCHAR(MAX) = ''

SET @SQL = '

;WITH                   
      Pivot' + @ErrorCategory + 'CheckCorrect AS (             
            SELECT DISTINCT      
                  #tCustomerQCResult.Country AS Country,
                  COUNT(DISTINCT #tCustomerQCResult.Customer) AS Correct' + @ErrorCategory + 'CheckPerCC
            FROM      
                  #tCustomerQCResult
            WHERE      
                  #tCustomerQCResult.' + @ErrorCategory + 'Check = ''Ok''
            GROUP BY      
                  #tCustomerQCResult.Country
            )      
            ,      
      Pivot' + @ErrorCategory + 'CheckIncorrect AS (             
            SELECT DISTINCT      
                  #tCustomerQCResult.Country AS Country,
                  COUNT(DISTINCT #tCustomerQCResult.Customer) AS Incorrect' + @ErrorCategory + 'CheckPerCC
            FROM      
                  #tCustomerQCResult
            WHERE      
                  #tCustomerQCResult.' + @ErrorCategory + 'Check <> ''Ok''
            GROUP BY      
                  #tCustomerQCResult.Country
            )      
            ,      
      Pivot' + @ErrorCategory + 'CheckIncorrectNew AS (             
            SELECT DISTINCT      
                  #tCustomerQCResult.Country AS Country,
                  COUNT(DISTINCT #tCustomerQCResult.Customer) AS Incorrect' + @ErrorCategory + 'CheckPerCCNew
            FROM      
                  #tCustomerQCResult
            WHERE      
                  #tCustomerQCResult.New' + @ErrorCategory + 'Error = ''New' + @ErrorCategory + 'Error''
            GROUP BY      
                  #tCustomerQCResult.Country
            )      
            ,      
      Pivot' + @ErrorCategory + 'CheckTotal AS (             
            SELECT DISTINCT      
                  #tCustomerQCResult.Country AS Country,
                  COUNT(DISTINCT #tCustomerQCResult.Customer) AS Incorrect' + @ErrorCategory + 'CheckPerCC
            FROM      
                  #tCustomerQCResult
            GROUP BY      
                  #tCustomerQCResult.Country
            )      
                  
SELECT                  
      #CustomerQCCountries.LAND1 AS Country,            
      ISNULL(Pivot' + @ErrorCategory + 'CheckCorrect.Correct' + @ErrorCategory + 'CheckPerCC,0) AS Correct' + @ErrorCategory + ',            
      ISNULL(Pivot' + @ErrorCategory + 'CheckIncorrect.Incorrect' + @ErrorCategory + 'CheckPerCC,0) AS Incorrect' + @ErrorCategory + ',            
      ISNULL(Pivot' + @ErrorCategory + 'CheckIncorrectNew.Incorrect' + @ErrorCategory + 'CheckPerCCNew,0) AS Incorrect' + @ErrorCategory + 'New,            
      ISNULL(Pivot' + @ErrorCategory + 'CheckTotal.Incorrect' + @ErrorCategory + 'CheckPerCC,0) AS GrandTotal' + @ErrorCategory + ',            
      ISNULL(FORMAT(ISNULL(Pivot' + @ErrorCategory + 'CheckCorrect.Correct' + @ErrorCategory + 'CheckPerCC,0) * 1.0 / NULLIF(ISNULL(Pivot' + @ErrorCategory + 'CheckTotal.Incorrect' + @ErrorCategory + 'CheckPerCC,0) * 1.0, 0),''p''),''N/A'') AS ''Percent''            
FROM                  
      #CustomerQCCountries            
LEFT JOIN                  
      Pivot' + @ErrorCategory + 'CheckCorrect ON #CustomerQCCountries.LAND1 = Pivot' + @ErrorCategory + 'CheckCorrect.Country            
LEFT JOIN                  
      Pivot' + @ErrorCategory + 'CheckIncorrect ON #CustomerQCCountries.LAND1 = Pivot' + @ErrorCategory + 'CheckIncorrect.Country            
LEFT JOIN                  
      Pivot' + @ErrorCategory + 'CheckIncorrectNew ON #CustomerQCCountries.LAND1 = Pivot' + @ErrorCategory + 'CheckIncorrectNew.Country            
LEFT JOIN                  
      Pivot' + @ErrorCategory + 'CheckTotal ON #CustomerQCCountries.LAND1 = Pivot' + @ErrorCategory + 'CheckTotal.Country            
ORDER BY                  
      #CustomerQCCountries.LAND1 '

--PRINT (@SQL)
EXEC(@SQL)

Open in new window


read more about exec -
https://docs.microsoft.com/en-us/sql/t-sql/language-elements/execute-transact-sql

read more about executesql -  
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql

A good read -
http://www.sommarskog.se/dynamic_sql.html
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ganesh GuruduSenior ConsultantCommented:
I am not sure, but does it make any difference ? by replacing the variable with "VAT" to "Direct Debit".
as this is SQL code changed and there is no change in output of this.

However, if you want make separate version of it. You can maintain the 2nd version by replacing it with Direct_Debit .

NOTE: space will not work here. you many need to use without space like "DirectDebit" or "Direct_Debit"
0
csehzIT consultantAuthor Commented:
That is simply brilliant, thank you
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.