如果在游标内部有条件写怎么写？ [英] How to write if conditon inside cursor?

问题描述

我的查询

This my query

Declare 
@chemcode varchar(50),
@chemname varchar(50),
@chemtype varchar(50),
@Chemfscode varchar(50),
@Chemsubareacode varchar(50),
@chemsubareaname varchar(50),
@jsonchem varchar(max),
@fscode varchar(50)
set @fscode='TSM001' 
set @jsonchem=      
  '{      
   "Status": "Success",      
   "Status Message": "Service Call Successful"      
   "chemist":      
   [      
   {'    
declare c cursor for    
select sd.c_code,sd.c_name,sd.n_type,cd.c_fs_code,sub.c_code,sub.c_name from Tbl_Doc_Stock_Chem_Add_Mst sd    
join tbl_cust_div cd on cd.c_cust_code=sd.c_code    
join Tbl_Sub_Area_Mst sub on sub.C_Code=sd.C_Subarea_Code    
left join Tbl_Category_Mst ca on ca.C_Code=sd.C_Category    
left join tbl_doc_grade_mst dc on dc.c_code=cd.c_grade    
where sd.C_FsCode=@fscode and sd.N_type=3 and sd.n_deleted=0 and n_status=0    
open c    
fetch next from c into @chemcode,@chemname,@chemtype,@Chemfscode,@Chemsubareacode,@chemsubareaname    
while @@FETCH_STATUS=0    
begin    
set @jsonchem=
@jsonchem +       
    '       
     "Chemist Code":"' + @chemcode + '"      
     "Chemist Name":"' + @chemname + '"      
     "Type":"' + @chemtype + '"      
     "Fscode":"' + @Chemfscode + '"      
     "Subarea Code":"' + @Chemsubareacode + '"      
     "SubArea Name":"' + @chemsubareaname + '"      
         
    },      
    {      
    ' 
  fetch next from c into @chemcode,@chemname,@chemtype,@Chemfscode,@Chemsubareacode,@chemsubareaname    
     
  end      
  close c      
  deallocate c      
  set @jsonchem=LEFT(@jsonchem,len(@jsonchem)-14)      
  set @jsonchem = @jsonchem + ']      
        }'      
  select @jsonchem

这里我想要一件事，如果游标返回null vale意味着没有值，我想显示一个消息，如'服务是不成功的'怎么能写这个请求帮助我。最好的解决方案会很棒。



我尝试过：



我现在已经尝试过显示光标返回的数据。如果它没有返回任何我想要显示的值一条消息如何写

here i want one thing that if cursor returns null vale means there is no value for that i want to display one mesageg like 'Service is unsuccessfull' how can write for this please plese help me out.best solution would be greatful.

What I have tried:

I have tried til now to displaying data that cursor returned.if it is not returned any value i want to display one message that mesage how to write

推荐答案

有几种方法可以处理sql中的空值：



ISNULL（字段，替代）

There are a few ways to handle null values in sql:

ISNULL(field,alt)

 select ISNULL(a,'is null')
 from (select null as a
       union select 'not null')

-- output:
-- |a    |
-- 'is null' (null was replaced
-- 'not null' (value was not null so value was used)

COALESCE（字段，alt1 [，alt2 [，alt3 [，等]]]）

与ISNULL的工作原理相同但是alts可以是任何长度甚至是表格​​列。首先使用非null。



CASE WHEN

不太适合只是一个普通的空值，因为你可以选择几种情况：

COALESCE(field,alt1[, alt2[, alt3[, etc]]])
Works the same as ISNULL but the alts can be any length or even a table column. First non-null is used.

CASE WHEN
Not really ideal for just a plain null value as you can select several cases:

SELECT CASE
   WHEN a is null THEN 'is null'
   WHEN a = 'not null' THEN 'something else'
   ELSE 'optional default'
END
from (select null as a
      union select 'not null')

然后就是可靠的IF。与只能在查询中使用的后三个不同，IF只能在查询之外使用：

And then there is the ever reliable IF. Unlike the last three which can only be used within a query, IF can only be used outside of a query:

 IF (SELECT Count(1) from (select null as a union select 'not null') WHERE a is null) > 0
BEGIN
  raiserror('there''s a null',16,1) --or whatever the format is.
ELSE
  --do the other thing
END

我希望能够解决这个问题:)

Andy

I hope that sheds some light on the issue :)
Andy

这篇关于如果在游标内部有条件写怎么写？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！