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
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.