I'm using LLBLGen Pro 2.5, the Self-Servicing model, and the SQL Server/C# templates. I'm having a problem calling a stored procedure which allows null values to be passed as input parameters. Basically, it appears I need to pass DBNull.Value for these parameter values but am unable to do it using the templates as-is.

I realize I can change the templates myself, but our client is averse to any in-house customization to third-party tools. In addition, in the past we've found it a bit cumbersome to ensure that our developers are all using the same template versions when we modify them.

I see where this was apparently address with the OracleMS templates here: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=7926&HighLight=1

What I'm wondering is if this same "fix" can be applied to the stock SQL Server templates.

Hi Eric.

What LLBLGenPro version are you using?

That fix was required for oracle, not sqlserver. What happens if you pass null to the parameter? You should check the checkbox for the parameter which is nullable in the designer. The code then will generate the parameter to have a nullable<T> type instead of the T type. You then should simply pass null.

It would also be great if you could elaborate a bit more about what exactly goes wrong in YOUR situation. We now don't have enough information.

I checked the "Nullable" checkbox for the SP parameters.

When I pass null into the generated SP method, I get the following error message: "Procedure or function 'TestProc' expects parameter '@Param1', which was not supplied"

I've attached a sample solution that illustrates the problem. It's a very simple project that includes a script for a stored procedure that accepts 1 parameter.

After some more testing, I found that if I provide a default value of NULL for the parameter within the stored procedure, everything works fine. If I don't provide a default value, then the call returns the error message stated above.

BTW, thanks for such a great product and the great support. I've been a loyal LLBLGen user since 1.0.2004. Keep up the great work!

After some more testing, I found that if I provide a default value of NULL for the parameter within the stored procedure, everything works fine. If I don't provide a default value, then the call returns the error message stated above.

Should we consider the thread as solved, then?

Well, not in my opinion. Setting a default value for a parameter within the stored procedure seems more like a workaround than a proper fix. The proper approach would be for LLBLGen to send DBNull.Value to these parameters, but there doesn't appear to be any way to do this without modifying the templates. When you set a parameter value to null in the method call, it appears that no value is passed for that parameter to the stored procedure, which is why the default value comes into play.

It seems to me that the approach Otis took with the OracleMS templates fix would be the best solution. If you pass null in for a parameter value, the generated LLBLGen code would perform a replacement and supply DBNull.Value for the parameter value.

Like I said, I realize I could make these template changes myself, but due to restrictions put on us by our client, we aren't allowed to make changes ourselves to third-party tools (I know, I know. I've explained it to them over and over again, but they consider it a modification of the tool itself).

I think this was a design decision, cause people asked for the suopport of SPs having parameters with default values, and the only way to use the default value in a SP, is to path nothing and this is when the user passes null as the value of the parameter in the method call.

We''ll further investifate this issue and get back to you.

Good point. Hopefully, you guys can come up with a way to support both situations - supporting default parameter values and passing a SQL null (DBNull.Value).

Anyway, the default parameter value approach is a sufficient workaround for my current dilemma, but in the long-run it'd be nice to actually PASS a SQL null value to a parameter instead of relying on a default value. I can see where that could be a problem in the future (i.e. where you might want to actually have a default value other than NULL).

Thanks for the great attention and support!

Hi,

Did any firther investigation/development happen around this issue as I am running into the same problem of no default values specified for nullable parameters.

Thanks.

Please create a new thread instead of using this old one, as per: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=7717

And explain your problem in more details, as I think the following thread explained all aspects around this issue.

If there were a solution to this problem, I think the templates should be modified to support it in the following way:

When calling a the stored proc, if you pass null in - the parameters are not set and the proc would use the default values:

MyStoredProc(23, 15, null) ; //third parameter would use proc default.

However, if you actually wanted null to be sent to the stored proc so it would use null and not the default you would pass DBNull.

MyStoredProc(23, 15, DBNull.Value);

This handles both situations nicely. If LLBLGen folks do now want to make this template change, then you could make this change easily.

I don't think that will compile, as DBNull.Value is of a specific type.