asp中case的应用

select case groupid
case "100"
sql="SELECT (case agentName when '"&agentName&"' then '0' else '1' end) as orderid ,id,CustomName, tel, agentName, Createtime,Address,latesttime,modifyname FROM dbo.HouseCusManage where 1=1 "
case "2"
sql="SELECT (case agentName when '"&agentName&"' then '0' else '1' end) as orderid ,id, tel,CustomName, agentName, Createtime, Address, latesttime, modifyname  FROM dbo.HouseCusManage where 1=1 "
case "1"
sql="SELECT '1' as orderid ,id,CustomName, tel, agentName, Createtime, Address, latesttime,modifyname FROM dbo.HouseCusManage where agentName = '"&agentName&"'"
end select
'select (case userid  when  '自己的ID' then '0'  else userid  end)  as orderid    from table  order by orderid

if Request("search")<> Empty then
   if trim(request("CustomName"))<>"" then
    
  sql=sql+"and CustomName like '%"&request("CustomName")&"%'"
 
   end if
   if trim(request("tel"))<>"" then
    sql=sql+"and tel='"&request("tel")&"'"
   
   end if
   if trim(request("Address"))<>"" then
      
    sql=sql+"and Address like '%"&request("Address")&"%'"
   
   end if
   if begintime<>"" then
  
    
    sql=sql+"and  Createtime >= Convert(datetime, '" & begintime & " 00:00:00')"
   
 end if
 
 if endtime<>"" then
    
    sql=sql+"and Createtime <= Convert(datetime, '" & endtime & " 23:59:59')"
   
 end if
end if
sql=sql+"order by orderid ,id desc "
set rs=server.CreateObject("adodb.recordset")

rs.open sql,conn,1,1

'response.write "暂时无客户电话资料！"
 'end if
%> 

case的应用：

SELECT au_fname, au_lname,
   CASE state
      WHEN 'CA' THEN 'California'
      WHEN 'KS' THEN 'Kansas'
      WHEN 'TN' THEN 'Tennessee'
      WHEN 'OR' THEN 'Oregon'
      WHEN 'MI' THEN 'Michigan'
      WHEN 'IN' THEN 'Indiana'
      WHEN 'MD' THEN 'Maryland'
      WHEN 'UT' THEN 'Utah'
        END AS StateName
FROM pubs.dbo.authors
ORDER BY au_lname