sqlbigintenterprise-library-5

Return Bigint in a stored procedure


I have a stored procedure which has to return a bigint as output. below if the definition.

In the body, I'm inserting a row in a table and returning the identity using @@Identity in @LogID output variable.

Everything works except the return part. I have tried casting, converting, selecting and setting the @LogID to @@identity but no luck.

The stored proc is called by enterprise library's Logging block. I'm using Write method of Logwriter class. This is a standard class provided by Microsoft and we have not modified it. I can't show you the code which calls the procedure as this is a DLL and don't have the source code. Anyway, I'm pretty sure it's not the C# code as I get a SQLException so it is something in the sql. The code below is for brevity and there are lots of other columns which I have removed.They are all provided as input parameters.

I'm sure it's a stupid thing, but I'm somehow missing it.

CREATE PROCEDURE [dbo].[WriteLog]
(
    @EventID int, 
    @Priority int, 
    @LogId bigint OUTPUT
)
    INSERT INTO [Log] (EventID, Priority) 
    VALUES(@EventID,@Priority)

    SET @LogID = @@identity
Go

Solution

  • Stored procedures can only return int. So you will need to use the output parameter.

    declare @CapturedLogID bigint;
    exec dbo.WriteLog @EventID = 42, @Priority = 1337, @LogID = @CapturedLogID output;
    

    In the above example, after the procedure executes, @CapturedLogID stores the value set within the stored procedure (@@identity, which, as others have pointed out, should be changed to scope_identity() immediately).


    Edit: From C#, to call the procedure with an out parameter:

    using (var cmd = new SqlCommand()) {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "dbo.WriteLog";
        cmd.Parameters.Add("EventID", SqlDbType.Int).Value = 42;
        cmd.Parameters.Add("Priority", SqlDbType.Int).Value = 1337;
        cmd.Parameters.Add("LogID", SqlDbType.BigInt).Direction = ParameterDirection.Output;
        cmd.ExecuteNonQuery();
    
        long logID = (long)cmd.Parameters["LogID"].Value;
    }
    

    Note that I've only included the code for setting up and executing the SqlCommand object.