How to write if conditon inside cursor?

Question

0.00/5 (No votes)

See more:

This my query

SQL

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

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

Posted 14-Feb-16 20:20pm

Member 11337367

Updated 14-Feb-16 20:41pm

Tomas Takac

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Andy Lanng · Answer 1 · 2016-02-14T22:49:00

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

ISNULL(field,alt)

SQL

 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(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:

SQL

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')

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:

SQL

 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

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