Professional Web Applications Themes

IDENTITY not working - Microsoft SQL / MS SQL Server

Hi, My stored procedure calls another stored procedure in a CURSOR and inserts data into a Temporary table. When I do this, I try to update a new column by making the use of IDENTITY column, but its not working properly. I am giving the snippet of my code below. Can someone tell me why the IDENTITY is returning Null? Thanks in advance, Kamlesh ---------------------------------------- Declare Cd_policy nvarchar(20) drop table temp_sur create table temp_sur( PId [smallint] IDENTITY (1, 1) NOT NULL , SurrenderValue decimal(10,2), UnexpiredPremium decimal(10,2), LapseInterest decimal(10,2), DelayedInterest decimal(10,2), IncomeTax decimal(10,2), ResidenceTax decimal(10,2), MiscIncomeTax decimal(10,2), MiscRecidenceTax decimal(10,2), TotalAdvancePremiumDiscount decimal(10,2), ...

  1. #1

    Default IDENTITY not working

    Hi,

    My stored procedure calls another stored procedure in a CURSOR and
    inserts data into a Temporary table. When I do this, I try to update
    a new column by making the use of IDENTITY column, but its not working
    properly.

    I am giving the snippet of my code below.

    Can someone tell me why the IDENTITY is returning Null?

    Thanks in advance,
    Kamlesh

    ----------------------------------------

    Declare Cd_policy nvarchar(20)

    drop table temp_sur

    create table temp_sur(
    PId [smallint] IDENTITY (1, 1) NOT NULL ,
    SurrenderValue decimal(10,2),
    UnexpiredPremium decimal(10,2),
    LapseInterest decimal(10,2),
    DelayedInterest decimal(10,2),
    IncomeTax decimal(10,2),
    ResidenceTax decimal(10,2),
    MiscIncomeTax decimal(10,2),
    MiscRecidenceTax decimal(10,2),
    TotalAdvancePremiumDiscount decimal(10,2),
    AdvancePremium decimal(10,2),
    TotalDeductions decimal(10,2),
    AmountPaid decimal(10,2),
    Policynumber nvarchar(20))

    Open cursor_forSurrender

    Fetch cursor_forSurrender Into Cd_policy

    WHILE FETCH_STATUS = 0

    Begin

    Insert into temp_sur(SurrenderValue, UnexpiredPremium, LapseInterest,
    DelayedInterest,
    IncomeTax, ResidenceTax, MiscIncomeTax, MiscRecidenceTax,
    TotalAdvancePremiumDiscount,
    AdvancePremium, TotalDeductions, AmountPaid) Exec sp_SurrenderValue
    Cd_policy

    -- The stored proc sp_SurrenderValue takes a Single INPUT parameter
    and returns a Row containing all the fields defined in the above
    temporary table. The only 2 new fields in this temporary table are
    PId and Policynumber.

    PRINT "Policy # : "
    PRINT Cd_policy
    PRINT "Identity: "
    PRINT IDENTITY -- This is returning NULL
    UPDATE temp_sur Set Policynumber = Cd_policy where PId = (SELECT
    IDENTITY)


    Fetch NEXT FROM cursor_forSurrender Into Cd_policy

    End

    CLOSE cursor_forSurrender

    DEALLOCATE cursor_forSurrender
    Kamlesh Guest

  2. #2

    Default Re: IDENTITY not working

    null value indicates that the last insert was to a table that did not have
    an identity column. Althought it is possible that you might have
    encountered a bug, I would lean towards an error in the logic or an error in
    assumptions. Perhaps the table (when finally used) does not actually have
    an identity column. Another possibility is that the inserted table has
    triggers of its own. Lastly if you are using SQL2k there have been
    enhancements - checkout scope_identity

    "Kamlesh" <com> wrote in message
    news:google.com... 


    Scott Guest

  3. #3

    Default Re: IDENTITY not working

    In addition to the other replies: make sure that you do not have any
    triggers on the target table (in this case table temp_sur).

    You might want to try using SCOPE_IDENTITY() instead of identity.

    Gert-Jan


    Kamlesh wrote: 
    Gert-Jan Guest

Similar Threads

  1. Replies: 2
    Last Post: March 18th, 03:39 AM
  2. Replies: 6
    Last Post: April 16th, 10:37 PM
  3. Replies: 5
    Last Post: November 8th, 05:25 PM
  4. IDENTITY is 0 ?
    By A Ratcliffe in forum ASP Database
    Replies: 6
    Last Post: October 22nd, 12:26 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139