CODEWITHSUNDEEP
sqlsql-servert-sqlcasesql-server-2014
Furkan Gözükara
Furkan Gözükara

Reputation: 23800

How to write multiple if case then else situation as a single case

Alright title is hard for me to express so it can be edited

My question is can i write below query in a shorter way

At the all cases the when is same

SQL server 2014

Here the query

    update
   tblCrawlUrls 
set
   cl_LastCrawlDate = case 
      when Len(@cl_CrawlSource) > 2 then SYSUTCDATETIME() 
      else cl_LastCrawlDate  
   end ,
   cl_TotalCrawlTimes = case 
      when Len(@cl_CrawlSource) > 2 then @cl_TotalCrawlTimes 
      else cl_TotalCrawlTimes  
   end ,
   cl_Ignored_By_Containing_Word = case 
      when Len(@cl_CrawlSource) > 2 then @cl_Ignored_By_Containing_Word 
      else cl_Ignored_By_Containing_Word  
   end ,
   cl_PageProcessed = case 
      when Len(@cl_CrawlSource) > 2 then 0 
      else cl_PageProcessed 
   end ,
   cl_CertainlyNotProductPage = case 
      when Len(@cl_CrawlSource) > 2 then @cl_CertainlyNotProductPage 
      else cl_CertainlyNotProductPage 
   end ,
   cl_CrawlSource = case 
      when Len(@cl_CrawlSource) > 2 then @cl_CrawlSource 
      else cl_CrawlSource 
   end ,
   cl_FoundLinksCount = case 
      when Len(@cl_CrawlSource) > 2 then @cl_FoundLinksCount 
      else cl_FoundLinksCount 
   end ,
   cl_DuplicateUrl_ByCanonical = case 
      when Len(@cl_CrawlSource) > 2 then @cl_DuplicateUrl_ByCanonical 
      else cl_DuplicateUrl_ByCanonical 
   end                        
where
   cl_CrawlNormalizedUrl=@cl_CrawlNormalizedUrl;

Upvotes: 0

Views: 76

Answers (1)

Philip Kelley
Philip Kelley

Reputation: 40309

Wouldn't this work?

IF len(@cl_CrawlSource) > 2
    UPDATE tblCrawlUrls 
     set
       cl_LastCrawlDate              = SYSUTCDATETIME() 
      ,cl_TotalCrawlTimes            = @cl_TotalCrawlTimes 
      ,cl_Ignored_By_Containing_Word = @cl_Ignored_By_Containing_Word 
      ,cl_PageProcessed              = 0 
      ,cl_CertainlyNotProductPage    = @cl_CertainlyNotProductPage 
      ,cl_CrawlSource                = @cl_CrawlSource 
      ,cl_FoundLinksCount            = @cl_FoundLinksCount 
      ,cl_DuplicateUrl_ByCanonical   = @cl_DuplicateUrl_ByCanonical 
     where cl_CrawlNormalizedUrl = @cl_CrawlNormalizedUrl

Unless I'm missing something, every case statement has the same clause len(@cl_CrawlSource) > 2, and if that clause is not met the column is left unchanged, with the whole only affecting those rows where cl_CrawlNormalizedUrl = @cl_CrawlNormalizedUrl

Upvotes: 5

Related Questions