CODEWITHSUNDEEP
sqlt-sql
hari
hari

Reputation: 21

My id is not returning in the else if condition

My id is not returning in the else if condition. It is showing that 'there is no row at position 0'.

   USE [ctsdev]
    GO

    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    alter PROCEDURE [dbo].[usp_incen]   
    (                   

    @ConsultantName varchar(50) ,                    
    @ClientName varchar(50) ,        
    @StartDate varchar(50),       
    @PositionName varchar(20) ,    
    @Location varchar(20) ,         
    @Job_Status varchar (20),                        
    @BenchMarketing varchar(1) ,                    
    @Placement varchar(1),    
    @CompanyName varchar(20),          
    @Durations varchar(20),               
    @DurationofProject varchar(10),  
    @Last_Updated_Date nvarchar(50),
    @Rec_Name varchar(50),  
    @id int output   
    )    
    AS                  
    BEGIN     

    SET NOCOUNT ON  

    /* checking whether the row with same session name and some id and updated date with remaining every fields as NULL exists*/  

    if (SELECT COUNT(*) as cnt from tbl_Empincentivenew1 WHERE
    id <> '' AND
    Rec_Name = @Rec_Name and
    Last_Updated_Date <> '' and
    ConsultantName IS NULL and
    ClientName IS NULL and
    DurationofProject IS NULL and
    Durations IS NULL and
    StartDate IS NULL and
    Location IS NULL and
    BenchMarketing IS NULL and
    Placement IS NULL and
    CompanyName IS NULL)=0



      BEGIN

      /*if not then id field,recruitername and updated date is inserted*/

      INSERT INTO  [tbl_Empincentivenew1](ConsultantName,ClientName,PositionName,CompanyName,Location,DurationofProject,Durations,BenchMarketing,Placement,Job_Status,Last_Updated_Date,StartDate,Rec_Name)                    
    OUTPUT INSERTED.id
    values(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,@Last_Updated_Date,NULL,@Rec_Name)   

    SET @id=SCOPE_IDENTITY()  
    RETURN @id  /*that id is returned to the front end*/
      ENd  
      /*if the id with rec name,updated date with remaining all fiels null exist return that particular id to front end*/
    ELSe if(SELECT COUNT(*) as cnt from tbl_Empincentivenew1 WHERE
    id <> '' AND
    Rec_Name = @Rec_Name and
    Last_Updated_Date <> '' and
    ConsultantName IS NULL and
    ClientName IS NULL and
    DurationofProject IS NULL and
    Durations IS NULL and
    StartDate IS NULL and
    Location IS NULL and
    BenchMarketing IS NULL and
    Placement IS NULL and
    CompanyName IS NULL)=1


      BEGIN 
     SET @id=SCOPE_IDENTITY()  /*return that existing id,instead for again inserting null values*/
    RETURN @id  
    END            

       END

    GO

This is my code for the first block the id value is getting inserted, second time whenever the same user logins he is not permitted to add new null records but the id for that null record is not returning.

Upvotes: 1

Views: 49

Answers (1)

Y.B.
Y.B.

Reputation: 3586

SCOPE_IDENTITY() returns last generated ID. In your ELSE branch you are not creating any new records, so SCOPE_IDENTITY() remains NULL.

Your ELSE statement can look like this:

ELSE
    SELECT @id = id
    FROM tbl_Empincentivenew1
    WHERE
        Rec_Name = @Rec_Name and
        Last_Updated_Date <> '' and
        ConsultantName IS NULL and
        ClientName IS NULL and
        DurationofProject IS NULL and
        Durations IS NULL and
        StartDate IS NULL and
        Location IS NULL and
        BenchMarketing IS NULL and
        Placement IS NULL and
        CompanyName IS NULL

Although I would first run this SELECT and then if @id is still NULL - run the INSERT and return @id = SCOPE_IDENTITY().

Upvotes: 1

Related Questions