SQL case usage

Because we need to display the queried data according to the conditions as needed, we use case to achieve this:

If the name is null and the ID is 0, this user is the quote user.

If the name is blank and the ID is not 0, the user name does not exist.

If the name is not blank, the user name is displayed.

View code

 1   Use   [  Xb_quotation_dev  ] 
 2   Go 
 3  
 4   /* * *** Object: storedprocedure [DBO]. [usp_gethistory_original_price] script Date: 02/08/2012 11:07:35 *****  */ 
 5   Set Ansi_nulls On 
 6   Go 
 7  
 8   Set Quoted_identifier On 
 9   Go 
10  
 11   --  ========================================================== =====  
  12   --  Author: <author, Name>  
  13   --  Create Date: <2012-02-08>  
  14   -- Description: <obtain the original quotation for steel, sheet metal, wire and charge materials>  
  15   --  @ Typeid = 1 indicates steel, sheet, and wire; @ typeid = 2 indicates charge  
  16   --  ========================================================== =====  
  17   Create   Procedure   [  DBO  ] .[  Usp_gethistory_original_price  ] 
 18   @ Startdate      Datetime ,
 19   @ Enddate      Datetime ,
 20   @ Typeid      Int 
 21   As 
 22  Begin 
 23       Set Nocount On ;
 24           If ( @ Typeid  =  1 )
 25               Begin 
 26                   With TM As (
27                               Select   Convert ( Varchar ( 10 ), Pricedate, 120 ) As Pricedate, adminuserid From Md_historyrawprice
 28                                   Where    Convert ( Varchar ( 10 ), Pricedate, 120 ) > =  Convert ( Varchar ( 10 ), @ Startdate , 120 )
 29                                   And   Convert ( Varchar ( 10 ), Pricedate,120 ) <=  Convert ( Varchar ( 10 ), @ Enddate , 120 )
 30                                   Group   By   Convert ( Varchar ( 10 ), Pricedate,120 ), Adminuserid
 31 )
 32                   Select TM. pricedate, TM. adminuserid,
 33                           Case   When (M. j_truename Is   Null   And TM. adminuserid =  0 ) Then  '  Quotation System  ' 
 34                           When (M. j_truename Is   Null   And TM. adminuserid <>  0 ) Then   '  The specified name does not exist.  ' 
 35                          Else M. j_truename
 36                           End   As J_truename
 37                           From TM Left   Join Member_info m On TM. adminuserid = M. unid Order   By TM. pricedate Desc             
 38              End 
 39           Else 
 40               Begin 
 41                   With TM As (
 42                               Select   Convert ( Varchar ( 10 ), Pricedate, 120 )As Pricedate, adminuserid From Charge_historyrawprice
 43                               Where    Convert ( Varchar ( 10 ), Pricedate, 120 ) > =  Convert ( Varchar ( 10 ),@ Startdate , 120 )
 44                               And   Convert ( Varchar ( 10 ), Pricedate, 120 ) <=  Convert ( Varchar ( 10 ), @ Enddate ,120 )
 45                               Group   By   Convert ( Varchar ( 10 ), Pricedate, 120 ), Adminuserid
 46 )
 47                   Select TM. pricedate, TM. adminuserid,
 48                          Case   When (M. j_truename Is   Null   And TM. adminuserid =  0 ) Then   '  Quotation System  ' 
 49                           When (M. j_truename Is   Null  And TM. adminuserid <>  0 ) Then   '  The specified name does not exist.  ' 
 50                           Else M. j_truename
 51                           End   As J_truename
 52                           From TMLeft   Join Member_info m On TM. adminuserid = M. unid Order   By TM. pricedate Desc 
 53               End 
 54   End 
 55  
 56   Go