search

dibley1973 / StoredProcedureFramework

A .Net framework for calling stored procedures
MIT License
4 stars 2 forks source link

Framework should handle empty lists for Table Value Parameters #4

Closed dibley1973 closed 8 years ago

dibley1973 commented 8 years ago

Awaiting expected behaviour from raiser of issue.

jadjare commented 8 years ago

Hi dibley1973,

Thanks for logging on my behalf.

Ideally the framework would create the request passing in an empty table rather than throw an exception.

There are use cases where it is logical that a TVP may be an empty table, for example, you may have a stored procedure like: dbo.GetOrdersExlcudingStatuses( @ExcludeStatuses OrderStatusIdTable readonly ) AS SELECT ... FROM CustomerOrders WHERE OrderStatusId NOT EXISTS (SELECT Id FROM @ExcludeStatuses)

Where the TVP is a restricive list, which may be empty is no restriction is to apply.

dibley1973 commented 8 years ago

Ok, created a test for this issue and the exception message that is returned is 'There are no records in the SqlDataRecord enumeration. To send a table-valued parameter with no rows, use a null reference for the value instead.'

This is not issues by my framework, but rather issues by a method in System.Data.SqlClient.

Investigating further, but it may be a case of passing no parameter at all, referring to this post: http://stackoverflow.com/a/6107942/254215

dibley1973 commented 8 years ago

Passing null instead of an empty list works. There is now a new test to prove this TableValueParameterStoredProcedureWithReturnAndEmptyList_DoesNotThrowException() which can be found in Dibware.StoredProcedureFramework.IntegrationTests.SqlConnectionTests

NaomiN commented 7 years ago

Can you please explain what should I use in my code? Please see https://social.msdn.microsoft.com/Forums/en-US/fc3d7a81-f543-4923-b67e-de81a39378a7/passing-tvps-to-sql-server-from-net?forum=transactsql