CREATE PROC dbo.spRaceInfo
    @UpdateType AS char(6),
    @RaceID AS int = NULL,
    @SectionID AS smallint = NULL,
    @RaceName AS varchar(50) = NULL,
    @ImagePath AS varchar(50) = NULL,
    @RaceDescription AS varchar(MAX) = NULL
AS

SET NOCOUNT ON;
SET XACT_ABORT ON;

BEGIN TRY
    BEGIN TRANSACTION;

        --Process a new race
        IF (@UpdateType = 'Insert')
        BEGIN
            INSERT INTO dbo.RaceInfo (SectionID, RaceName, ImagePath, RaceDescription)
            VALUES (@SectionID, @RaceName, @ImagePath, @RaceDescription);

            EXEC dbo.spAddSectionMenuItem @SectionID, 'Info', 'Races', 'Races', 'Race Information';

            INSERT INTO dbo.SiteUpdates (UpdatedSectionID, UpdatedSubSectionName, UpdatedItemName, UpdateType)
            VALUES (@SectionID, 'Races', @RaceName, @UpdateType);
        END
        ELSE IF (@UpdateType = 'Update')
        --Process a race update
        BEGIN
            UPDATE dbo.RaceInfo
            SET SectionID = @SectionID,
                RaceName = @RaceName,
                ImagePath = COALESCE(@ImagePath, ImagePath),
                RaceDescription = @RaceDescription
            WHERE RaceID = @RaceID;

            INSERT INTO dbo.SiteUpdates (UpdatedSectionID, UpdatedSubSectionName, UpdatedItemName, UpdateType)
            VALUES (@SectionID, 'Races', @RaceName, @UpdateType);
        END
        ELSE IF (@UpdateType = 'Delete')
        --Process race removal
        BEGIN
            DELETE dbo.RaceInfo
            WHERE RaceID = @RaceID;
        END

    COMMIT

END TRY
    
BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT 
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();

    RAISERROR (@ErrorMessage,
               @ErrorSeverity,
               @ErrorState);

    IF ((XACT_STATE()) = -1)
    BEGIN
        ROLLBACK;
    END
    ELSE IF ((XACT_STATE()) = 1)
    BEGIN
        COMMIT;
    END

END CATCH

quote:

---

Originally posted by sunitabeck

What I have observed (and it is just an observation, I have not seen it documented anywhere) is that the line numbers start immediately after the GO statement preceding the create statement of the stored proc.

So if you script the stored procedure (right-click on the stored proc name in object explorer in SSMS and select Script Procedure as -> Create To -> New Window) and delete everything starting at the first line up to the GO statement immediately preceding the CREATE PROCEDURE statement (but keep the blank lines between the GO and the CREATE statements), then the line numbers should match up what you see in the error message.

---

quote:

---

Originally posted by visakh16

Also it would make more sense for us if post how you're executing this proc ie what are values passed for parameters

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---

CREATE PROC dbo.spAddSectionMenuItem
	@SectionID AS smallint,
	@MenuType AS char(5),
	@LinkRoute AS varchar(50),
	@LinkName AS varchar(50),
	@LinkTitle AS varchar(250)
AS

SET NOCOUNT ON;
SET XACT_ABORT ON;

BEGIN TRY
    IF NOT EXISTS (SELECT 1 FROM dbo.SectionMenuItems WHERE SectionID = @SectionID and MenuType = @MenuType)
    BEGIN
	    INSERT INTO dbo.SectionMenuItems (SectionID, MenuType, LinkRoute, LinkName, LinkTitle)
	    VALUES (@SectionID, @MenuType, @LinkRoute, @LinkName, @LinkTitle);
    END
END TRY
BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT 
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();

    RAISERROR (@ErrorMessage,
               @ErrorSeverity,
               @ErrorState);

END CATCH