Friday, January 27, 2012

CLR Stored Proc with Output Value Returning NULL

I'm a little embarrassed to be writing a post about this but I figured there might be someone else out there making the same obvious mistake that might find this useful.

I recently created a CLR stored procedure that returns a value. Not a return code but an actual calculated output. To implement this in C# it is necessary to declare an output parameter in the method definition similar to the following.

public static void CLRSum(SqlInt32 val1, SqlInt32 val2, out SqlInt32 value)
   value = val1 + val2

As I alluded to in the title of this post, calls to my new CLR stored procedure where returning NULL. Below is how I was calling my CLR stored procedure from T-SQL.
EXECUTE dbo.CLRSum 4, 3, @value 

Notice anything missing? Took me a little while but I finally realized I was missing the output keyword. The correct way to call the stored proc is below.
EXECUTE dbo.CLRSum 4, 3, @value output


  1. Thank you... Thank you...Thank you...
    I can go home today, Friday evening knowing I have a clr proc that works ready for use in my project next week.

  2. OMG thank you! Couldn't figure out why my output parameter was always getting set to 0.