CODEWITHSUNDEEP
sqlsql-serversql-server-2000
shamim
shamim

Reputation: 6768

Delete and insert on same procedure

In my SQL stored procedure, I need to delete and insert on same query. My syntax is below. But my syntax fails to store data. Why does it fail? How do I solve this problem? My syntax is 

CREATE PROCEDURE spInsertCollectionInspectionHours  
    @StartDate DATETIME ,  
    @EndDate DATETIME ,  
    @ID BIGINT ,  
    @VesselName VARCHAR(80) ,  
    @VoyageNo VARCHAR(15) ,  
    @PortCode VARCHAR(20) ,  
    @Terminal VARCHAR(70) ,  
    @InspectionDate DATETIME ,  
    @InvoiceHours INT ,  
    @ManifestType INT ,  
    @Remarks NVARCHAR(200)  
AS   
    BEGIN    

        BEGIN  


            DELETE  FROM dbo.InspectionHours  
            WHERE    InspectionDate BETWEEN @StartDate AND @EndDate   

        END  

        BEGIN  


            SELECT  @ID = ISNULL(MAX(ID), 0) + 1  
            FROM    [InspectionHours]    
            INSERT  INTO [InspectionHours]  
                    ( [ID] ,  
                      [VesselName] ,  
                      [VoyageNo] ,  
                      [PortCode] ,  
                      [Terminal] ,  
                      [InspectionDate] ,  
                      [InvoiceHours] ,  
                      [ManifestType] ,  
                      [Remarks]  
                    )  
            VALUES  ( @ID ,  
                      @VesselName ,  
                      @VoyageNo ,  
                      @PortCode ,  
                      @Terminal ,  
                      @InspectionDate ,  
                      @InvoiceHours ,  
                      @ManifestType ,  
                      @Remarks  
                    )    
        END  
    END

If have any questions please ask. Thanks in advance.

Upvotes: 1

Views: 2791

Answers (2)

vikas
vikas

Reputation: 21

check your id field identity property is true or not if it's true or yes then no need to give id in insert statement

Upvotes: 2

Abe Miessler
Abe Miessler

Reputation: 85096

Your syntax is fine. This should not produce and error.

Your insert statement is also fine. If it is not throwing an error then something else is going on. Are you sure you are passing parameters? Are you sure you are looking in the correct server/db/table and using the correct query to check? Are you positive it's not throwing an error?

Upvotes: 0

Related Questions