Return Bigint in a stored procedure

Question

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

Don't use @@IDENTITY here. You should use SCOPE_IDENTITY instead. If you have a trigger on your Log table that does an insert with an identity column your procedure will get that value. What do you mean the return part doesn't work? How are you calling this? — Sean Lange, Sep 18 '14 at 14:40
I'd lay money on your having forgotten to *also* include the keyword `OUTPUT` in the place where you're *calling* the stored procedure. But you've not shown us that bit of code. — Damien_The_Unbeliever, Sep 18 '14 at 14:41
@SeanLange No I don't have a trigger on Log Table. This code is part of my implementation of enterprise library's logging block. The WriteLog procedure should return a Output as per Library's requirement. My database is about to cross the limit of an Int for LogID column and I need change it to bigint for the logging to continue. The code works if output is Int but not when Bigint — Ruchin Munjal, Sep 18 '14 at 14:51
@Damien_The_Unbeliever you would loose your money... :) It's used by enterprise library..lol — Ruchin Munjal, Sep 18 '14 at 14:51
The point is not that you don't have a trigger on the Log table today. Using @@IDENTITY is the wrong function here. It does not directly answer your question but you should use SCOPE_IDENTITY. http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/ — Sean Lange, Sep 18 '14 at 14:53
@SeanLange I see what you are saying. Trying now. Will let you know. — Ruchin Munjal, Sep 18 '14 at 15:40
@SeanLange Didn't work. As you said, it's not the problem here, but a good point. — Ruchin Munjal, Sep 18 '14 at 16:03
sigh. Now you say you are getting an exception. Care to share those details? I am guessing this library is passing an int and your are passing a value larger than an int and you are getting a conversion error. Seeing the error text would provide TONS of information. — Sean Lange, Sep 18 '14 at 16:09

md4 · Accepted Answer · 2014-09-18T15:50:02.943

4

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.

edited Sep 18 '14 at 15:50

answered Sep 18 '14 at 14:50

md4

1,609
11
13

@LogID is the output parameter – Ruchin Munjal Sep 18 '14 at 14:52
I know. You declare a variable to capture the parameter so that you can use it after the stored procedure call. I will edit my answer and rename the variable to make the distinction more obvious. – md4 Sep 18 '14 at 14:54
I'm calling the SP from the C# code and not executing it like you have shown. – Ruchin Munjal Sep 18 '14 at 15:37
So how are you executing it? Without seeing the calling code we are all just guessing here. – Sean Lange Sep 18 '14 at 15:43
Answer has been updated to include C# code. You should have mentioned that detail in your question. – md4 Sep 18 '14 at 15:51
@MattRankin : Thanks for the answer, but this is handled internally by enterprise library and not something that I have coded. I have just configured it to use this SP and the output is what the internal code expects and its is used to update another table, which is not my concern (yet) as it's failing on the first step itself. I have updated the question as you suggested – Ruchin Munjal Sep 18 '14 at 15:58
If the library can only process the return value, and you need that to be a `bigint`, then I'm afraid you're out of luck. – md4 Sep 19 '14 at 09:20
What would happen on return, if `LogID` value is `null`? – AaA Aug 24 '18 at 04:49

Return Bigint in a stored procedure

1 Answers1