NHibernate INSERT TO SQL Calculated field SET NOCOUNT

Question

I need to surpress messages output from a SQL function. As in 1 row affected. I can't use SET NOCOUNT as it's invalid in a function.

Anyone know a way to do this?

Thanks.

EDIT

I was trying to limit the background information in an attemp to boil the problem down to it's essence. But I'll expand. I'm using MSSQL2005 and NHibernate to insert a record in to a SQL table. On the table I have a computed column that runs the function which is reporting back 1 row affected.

I didn't really want to edit the NHibernate part of the process but it may be unavoidable.

What kind of database are you using? Sql Server, MySql, Oracle, etc? — Brandon Moore, Nov 15 '11 at 09:14
Does it not work, or are you not able to SET NOCOUNT from the code calling the function? — Brandon Moore, Nov 15 '11 at 09:17

score 2 · Accepted Answer · edited May 23 '17 at 11:55

2

A function that returns "(1 row affected)" will be part of a bigger query in a batch. It makes no sense to have SET NOCOUNT ON in the function

You need to do this:

SET NOCOUNT ON;
SELECT * FROM MyUDFTVF();

Note a stored procedure is simply a wrapper for this

CREATE PROC Whatever
AS
    SET NOCOUNT ON;
    SELECT * FROM MyUDFTVF();
GO

SET NOCOUNT ON is normally needed to stop triggers etc breaking client code: why do you need it here?

edited May 23 '17 at 11:55

Community

1
1

answered Nov 15 '11 at 09:53

gbn

422,506
82
585
676

1

Leading on from this answer I have decided to change from using NHibernate native to just putting a SP in place for the inserting the new record. This should solve the issue. Thanks for your help. – Mike Mengell Nov 15 '11 at 16:37

score 0 · Answer 2 · answered Nov 15 '11 at 09:50

The nocount setting is not available in functions.

Stored procedures allow you to set nocount. So converting the function to a stored procedure would solve the problem.

Otherwise, the calling code will have to set nocount. That shouldn't be hard, but might be tedious if the function is used in many places.

P.S. If you post the reason why suppressing the count messages is required, perhaps we can offer some more solutions.

NHibernate INSERT TO SQL Calculated field SET NOCOUNT

2 Answers2