[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

CASE WHEN for WHERE clause instead of IF

Posted on 2009-04-23
2
Medium Priority
?
185 Views
Last Modified: 2012-05-06
I am trying to replace IF with CASE, but keep getting syntax error.
Is there way to do something like:
-- SET @ParamSearchType = @SearchCriteria.value('(/params/searchtype)[1]', 'varchar(50)')
-- CASE @ParamSearchType
--   WHEN 'name'  THEN
--       SET @ParamValue = @SearchCriteria.value('(/params/email)[1]', 'varchar(100)')
--       SET @SQLStr  = @SQLStr + ' WHERE LastName = ''' + @ParamValue + ''''
.........
.........

My xml looks like this:


 <params>
   <searchtype>name</searchtype>
   <firstname>Bob</firstname>
   <lastname>Smith</lastname>
 </params>  
 
                                               OR

    <params>
      <searchtype>memberaccountid</searchtype>
      <memberaccountid>100010</memberaccountid>
   </params>


instead of technique with IF statements (btw it works fine the way I need):

Thank you in advance.
@SearchCriteria   xml                  
AS
BEGIN
 
  DECLARE @SQLStr varchar(8000)
  DECLARE @ParamSearchType varchar(50)
  DECLARE @ParamValue varchar(50)
  DECLARE @ParamValueFN varchar(50)
  DECLARE @ParamValueLN varchar(50)
 
  SET @SQLStr  = 'SELECT FirstName, LastName, MemberAccountID, Email.....'
 
 SET @SQLStr  = @SQLStr + ' FROM table'
 
 --Get SearchType value from searchtype parameter
 SET @ParamSearchType = @SearchCriteria.value('(/params/searchtype)[1]', 'varchar(50)')
   
  IF @ParamSearchType = 'name' 
  BEGIN 
        SET @ParamValueLN = @SearchCriteria.value('(/params/lastname)[1]', 'varchar(50)')
        --check if first name is provided
        IF @SearchCriteria.exist('/params/firstname') = 1
        BEGIN 
           SET @ParamValueFN = @SearchCriteria.value('(/params/firstname)[1]', 'varchar(50)')
           SET @SQLStr  = @SQLStr + '  WHERE FirstName = ''' + @ParamValueFN + ''' AND LastName = ''' + @ParamValueLN + ''' '
        END
        ELSE
           SET @SQLStr  = @SQLStr + ' WHERE LastName = ''' + @ParamValueLN + ''''   
  END  
 
  IF @ParamSearchType = 'memberaccountid' 
     BEGIN
       SET @ParamValue = @SearchCriteria.value('(/params/memberaccountid)[1]', 'varchar(50)')
       SET @SQLStr  = @SQLStr + ' WHERE MemberAccountID = ''' + @ParamValue + '''' 
     END
 
  EXEC (@SQLStr)
 
 
 
My xml looks like this:
 
 
 <params>
   <searchtype>name</searchtype>
   <firstname>Bob</firstname>
   <lastname>Smith</lastname>
 </params>   
  
                                               OR
 
    <params>
      <searchtype>memberaccountid</searchtype>
      <memberaccountid>100010</memberaccountid>
   </params>

Open in new window

0
Comment
Question by:shmoel
2 Comments
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 24217008
CASE is used for inline calculations; IF is used for logical branching in a batch.  So no, for what you are
trying to do, IF is the right syntax.
0
 

Author Closing Comment

by:shmoel
ID: 31573860
Thank you for your expertise.
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ready to get certified? Check out some courses that help you prepare for third-party exams.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question