• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 42
  • Last Modified:

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,
0
csehz
Asked:
csehz
1 Solution
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now