CODEWITHSUNDEEP
sql-serverstored-procedures
Chris
Chris

Reputation: 3129

How to return ID from stored procedure?

I have a stored procedure where I am passing an ID and wanting to return the selected ID that was given after passing in an ID. Here is what I came up with...

CREATE PROCEDURE [dbo].[usp_GetAdministratorHistoryIDByAdministratorID]
    @AdministratorID int,
    @AdministrationHistoryID int output
AS
DECLARE @ERROR_SEVERITY int,
        @MESSAGE varchar(1000),
        @ERROR_NUMBER int,
        @ERROR_PROCEDURE nvarchar(200),
        @ERROR_LINE int,
        @ERROR_MESSAGE nvarchar(4000);
begin try
    SELECT AdministrationHistoryID
    from [AdministrationHistory]
    where AdministratorID = @AdministratorID
    set @AdministrationHistoryID = AdministrationHistoryID
end try
BEGIN CATCH
    SET @ERROR_SEVERITY = ISNULL(ERROR_SEVERITY(),'');
    SET @ERROR_NUMBER = ISNULL(ERROR_NUMBER(),'');
    SET @ERROR_PROCEDURE = ISNULL(ERROR_PROCEDURE(),''); 
    SET @ERROR_LINE = ISNULL(ERROR_LINE(),'');
    SET @ERROR_MESSAGE = ISNULL(ERROR_MESSAGE(),'');

    -- Test if the transaction is uncommittable.
    IF (XACT_STATE()) = -1
        BEGIN
            --PRINT N'The transaction is in an uncommittable state. Rolling back transaction.'
            ROLLBACK TRANSACTION;
        END;

    -- Test if the transaction is active and valid.
    IF (XACT_STATE()) = 1
        BEGIN
            --PRINT N'The transaction is committable. Committing transaction.'
            COMMIT TRANSACTION;   
        END;

    SET @MESSAGE = 'Error Occured in Stored Procedure ' + cast(@ERROR_PROCEDURE as varchar(200)) + 
                    '; Line Number ' + cast(@ERROR_LINE as varchar) + 
                    '; Message: [' + cast(@ERROR_NUMBER as varchar) + '] - '
                    + cast(@ERROR_MESSAGE as varchar(255))

    RAISERROR(@MESSAGE, @ERROR_SEVERITY, 1);
END CATCH;

So what I am trying to achieve is by passing the AdministratorID, that it selects the AdministrationHistoryID from the AdministrationHistory table and I want to return that AdministrationHistoryID as an int.

I have tried executing this and I get an error

Msg 207, Level 16, State 1, Procedure usp_GetAdministratorHistoryIDByAdministratorID, Line 16
Invalid column name 'AdministrationHistoryID'.

I know that there is an AdministrationHistoryID because it's in the table.

Upvotes: 1

Views: 569

Answers (2)

M.Ali
M.Ali

Reputation: 69514

Mind you, you need to begin a transaction before you can commit it or rollback, Also committing your transaction in Catch block means you would not commit a transaction until something has gone wrong in the Try block, which doesnt make any sense.

You would only commit a transaction in try block and only rollback a transaction in catch block. since your control will never enter the catch block unless something went wrong in the try block.

A much simpler version of your stored procedure would be something like.....

CREATE PROCEDURE [dbo].[usp_GetAdministratorHistoryIDByAdministratorID]
    @AdministratorID int,
    @AdministrationHistoryID int output
AS
BEGIN
begin try
  BEGIN TRANSACTION;
     SELECT @AdministrationHistoryID = AdministrationHistoryID
     from [AdministrationHistory]
     where AdministratorID = @AdministratorID
  COMMIT TRANSACTION;
end try
BEGIN CATCH
  IF (@@TRANCOUNT > 0)
    BEGIN
      ROLLBACK TRANSACTION;
    END

Declare @MESSAGE NVARCHAR(4000);

    SET @MESSAGE = 'Error Occured in Stored Procedure ' + cast(ERROR_PROCEDURE() as varchar(200)) + 
                    '; Line Number ' + cast(ERROR_LINE() as varchar) + 
                    '; Message: [' + cast(ERROR_NUMBER() as varchar) + '] - '
                    + cast(ERROR_MESSAGE() as varchar(255))

    RAISERROR(@MESSAGE, ERROR_SEVERITY(), 1);
END CATCH

END

Upvotes: 2

Lukasz Szozda
Lukasz Szozda

Reputation: 175596

Use:

BEGIN TRY
    SELECT @AdministrationHistoryID = AdministrationHistoryID
    FROM [AdministrationHistory]
    WHERE AdministratorID = @AdministratorID;
END TRY

Keep in mind that if this query returns more than one row you will get last value.

Alternatively:

SET @AdministrationHistoryID = (SELECT AdministrationHistoryID
                                FROM [AdministrationHistory]
                                WHERE AdministratorID = @AdministratorID);

This may fail if more than one record is returned (add DISTINCT or TOP 1 and ORDER BY clause if needed.

Upvotes: 0

Related Questions