locked
How to see if row exist before doing insert RRS feed

  • Question

  • User-2060727494 posted

    I have this simple stored procedure which inserts a new row into a table:

    ALTER PROCEDURE [dbo].[RmaProblemLogInsert]
    @RmaNum varchar(50)
    ,@CreatedById varchar(500)
    ,@CreatedDate datetime
    ,@RmaComment varchar(max)
    AS
    BEGIN
    SET NOCOUNT ON;
    INSERT INTO RmaProblemLog VALUES (@RmaNum
    ,@CreatedById
    ,@CreatedDate
    ,@RmaComment)
    END

    But I only want to add a row if no row exists for this RmaNum.  What is the best way to do this?  Presently, I have a different stored proc that checks if row exists.  But I would rather do it within in this stored proc.

    Friday, November 6, 2015 9:07 AM

Answers

  • User77042963 posted
    ALTER PROCEDURE [dbo].[RmaProblemLogInsert]
    @RmaNum varchar(50)
    ,@CreatedById varchar(500)
    ,@CreatedDate datetime
    ,@RmaComment varchar(max)
    AS
    BEGIN
    SET NOCOUNT ON;
    If NOT Exists (Select 1 from RmaProblemLog WHERE RmaNum=@RmaNum)
    Begin
    INSERT INTO RmaProblemLog VALUES (@RmaNum
    ,@CreatedById
    ,@CreatedDate
    ,@RmaComment)
    End
    END

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 6, 2015 9:20 AM

All replies

  • User77042963 posted
    ALTER PROCEDURE [dbo].[RmaProblemLogInsert]
    @RmaNum varchar(50)
    ,@CreatedById varchar(500)
    ,@CreatedDate datetime
    ,@RmaComment varchar(max)
    AS
    BEGIN
    SET NOCOUNT ON;
    If NOT Exists (Select 1 from RmaProblemLog WHERE RmaNum=@RmaNum)
    Begin
    INSERT INTO RmaProblemLog VALUES (@RmaNum
    ,@CreatedById
    ,@CreatedDate
    ,@RmaComment)
    End
    END

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 6, 2015 9:20 AM
  • User-2060727494 posted

    That worked.  Thanks.  It was really inefficient the way i was doing it with 2 stored procs.  Again, thanks for your help!

    Friday, November 6, 2015 9:31 AM