locked
Sql Server Stored Procedure Error RRS feed

  • Question

  • I have Stored procedure with one output value called @ReportID we are getting this value as

    select @ReportID = @@identity  -- ReportID is the last # inserted into Reports

    This ReportID is Primarykey in Reports table. I have declared this as Data Type numeric(14,0)

    when i trying to execute the stored procedure i am getting below error..

    "Cannot insert the value NULL into column 'ReportID', table 'EVRep.dbo.AddReports'; column does not allow nulls. INSERT fails.

    The statement has been terminated."

    what could be the problem why its not working?

    Can anyone please help me on this.

    Thanks

     


    Diddi

    Wednesday, April 3, 2013 9:24 PM

Answers

  • Hello,

    Please , could you provide the definition ( CREATE PROCEDURE ) of your stored procedure and especially for its parameters ?

    Please , could you the version ( 2005,2008, 2008 R2, 2012 + last installed service pack )  of your SQL Server ?

    Are you working on a  database which is implied in replication ?

    I prefer to use SCOPE_IDENTTY  instead of @@IDENTITY. I would suggest you to have a look at http://msdn.microsoft.com/en-us/library/ms187342.aspx to have more information about potential problems.

    It is possible that a moderator moves your thread towards a more appropriate forum as it is not strictly related to SQL Server Data Access.

    Don't hesitate to post again for more help or explanations ( with my poor English , I am not always understandable in a full way )

    Have a nice day


    Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

    • Proposed as answer by Olaf HelperMVP Thursday, April 4, 2013 5:27 AM
    • Marked as answer by diddi10 Thursday, April 4, 2013 3:45 PM
    Wednesday, April 3, 2013 10:48 PM

All replies

  • Hello,

    Please , could you provide the definition ( CREATE PROCEDURE ) of your stored procedure and especially for its parameters ?

    Please , could you the version ( 2005,2008, 2008 R2, 2012 + last installed service pack )  of your SQL Server ?

    Are you working on a  database which is implied in replication ?

    I prefer to use SCOPE_IDENTTY  instead of @@IDENTITY. I would suggest you to have a look at http://msdn.microsoft.com/en-us/library/ms187342.aspx to have more information about potential problems.

    It is possible that a moderator moves your thread towards a more appropriate forum as it is not strictly related to SQL Server Data Access.

    Don't hesitate to post again for more help or explanations ( with my poor English , I am not always understandable in a full way )

    Have a nice day


    Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

    • Proposed as answer by Olaf HelperMVP Thursday, April 4, 2013 5:27 AM
    • Marked as answer by diddi10 Thursday, April 4, 2013 3:45 PM
    Wednesday, April 3, 2013 10:48 PM
  • select @ReportID = @@identity  -- ReportID is the last # inserted into Reports

    Hello Diddi,

    Papy is right, @@IDENTITY is the Problem. The function Returns the last identity value, inserted in the current Batch; if you haven't inserted a row, the functions Returns a NULL value and that's the reason for the error.

    Use better should use IDENT_CURRENT (Transact-SQL) to get the last identity for a specific table; independend of who/when it was inserted.


    Olaf Helper

    Blog Xing

    Thursday, April 4, 2013 5:27 AM