SQL Server DLL Access – How to Access External DLL’s Static Methods with Output Parameters

There is nothing special about OUTPUT parameters when it comes to "named parameters" or "ordinal parameters". In SQL Server this terminology this applies to the EXEC call and how you specify parameters there: not direction

• Ordinal = position must match and datatype must be compatible
• Named = assign the local value to the stored proc parameter name. Only datatype must be compatible

This is ordinal

declare @bob as int, @rich as char(20)
--OK
exec test @bob output, @rich output
GO

declare @bob as int, @rich as char(20)
--Fail
exec test @rich output, @bob output

This is named

declare @bob as int, @rich as char(20)
--OK
exec test
      @ID = @bob output,
      @mark = @rich output
GO

declare @bob as int, @rich as char(20)
--OK
exec test
      @mark = @rich output,
      @ID = @bob output

If you had a stored proc with parameters with defaults ...

create procedure test2
  @ID as INT output
 ,@filler1 tinyint = 0 --has default
 ,@mark as char(20) output
 ,@filler2 tinyint = 0 --has default
as
     select @ID = 5,@mark='test'
go

.. then you need the DEFAULT keyword for ordinal parameters if you have subsequent mandatory parameters

exec test2 @bob output, DEFAULT, @rich output --don't need to do anything for @filler2

For named parameters, it can be DEFAULT keyword or ignored because order is irrelevant

exec test2
      @ID = @bob output,
      @mark = @rich output,
      @filler2 = DEFAULT

exec test2
      @mark = @rich output,
      @ID = @bob output

And the obligatory MSDN link

You might be able to use the service broker, though that is probably overkill, as you state.

Alternately, if you don't want to install/manage an extra service for this one need, you could use xp_cmdshell or a CLR-based trigger to make an external call when needed, to a program that starts the desired process asynchronously (so, outside the transaction that called the trigger) and returns immediately. I would be more wary of this than simply putting some complex code in a trigger though – at least, with a trigger any good SQL Server DBA person would be able to support the code when you are not around rather than requiring some other specific programming experience.

When you say that polling simply isn't an option as an "up to 30 seconds" delay is unacceptable, is there a reason you can't poll more often? If the concern is the load the polling will impart on the server(s) then there are ways to keep the polling very light-weight. No matter what you are going to need the trigger (or something else) to decide if action is needed at the point rows are inserted - if you make this decision in a trigger and simply note it in a simple status table then you could poll every second-or-few to wait for that status marker being updated (a DB hit each second to perform a simple lookup like that once every few seconds (or even every second, or more often) is not going to impart significant load, unless you are using some cloud DB service that charges you by the query). Again, this is far from ideal, but it seems safer to me than the xp_cmdshell option.

Whatever you do, be careful to make sure failures in the process are flagged appropriately. As you are pulling the extra processing out of the main transaction, you can't rely on your existing exception handling to tell you that it failed for some reason – so, you might not know that you have a growing data consistency problem. The key advantage of the trigger here is that by staying in the same transaction you know your data is kept consistent, as one half won't be committed if the other half fails.