ExecuteNonQuery

Question

Post reply

ExecuteNonQuery

Steve Jones - SSC Editor

SSC Guru

Points: 736212
More actions
December 9, 2015 at 8:25 pm

#74800

Comments posted to this topic are about the item ExecuteNonQuery
December 9, 2015 at 10:34 pm

This was removed by the editor as SPAM

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

paul s-306273 SSChampion Points: 10730 More actions · Answer 1

Stewart "Arturius" Campbell (12/9/2015)
Nice one, thanks Steve.
Reminiscent of the C# method of the same name....

and C++

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 2

akljfhnlaflkj

SSC Guru

Points: 76202

December 10, 2015 at 6:30 am

#1845181

Thanks for the question. Had to do some research.

paul.knibbs SSCoach Points: 15320 More actions · Answer 3

Always thought this is a rather badly named function--ExecuteNonResults would be better, IMHO. ExecuteNonQuery implies it's not actually going to run the query, to my mind.

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 4

Thanks for the question.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

sknox SSChampion Points: 12388 More actions · Answer 5

paul.knibbs (12/10/2015)
Always thought this is a rather badly named function--ExecuteNonResults would be better, IMHO. ExecuteNonQuery implies it's not actually going to run the query, to my mind.

Well, a query is a question, which implies an answer.

That's why statement is a better general term to use for a unit of SQL code.

You wouldn't expect an INSERT or UPDATE statement to return results (unless you use an OUTPUT clause). They would be non-query SQL statements; hence ExecuteNonQuery.

paul.knibbs SSCoach Points: 15320 More actions · Answer 6

sknox (12/10/2015)
Well, a query is a question, which implies an answer.

So "NonQuery" implies you never asked the question in the first place, which is exactly the point I was making. ExecuteNonQuery is actually the equivalent of asking a rhetorical question--you ask the question expecting no answer--but ExecuteRhetorically would probably be just as confusing. 🙂

sknox SSChampion Points: 12388 More actions · Answer 7

paul.knibbs (12/10/2015)
sknox (12/10/2015)
Well, a query is a question, which implies an answer.
So "NonQuery" implies you never asked the question in the first place, which is exactly the point I was making. ExecuteNonQuery is actually the equivalent of asking a rhetorical question--you ask the question expecting no answer--but ExecuteRhetorically would probably be just as confusing. 🙂

No, as the rest of my comment explained, ExecuteNonQuery means Execute a non-query SQL statement, such as an INSERT or UPDATE. There's nothing rhetorical about that. It's just differentiating statements from queries.

I don't know for sure, but I'd guess the developers figured queries would be the majority of client-side activity (even actions such as inserting, updating, and deleting records are usually paired with a query to identify the records affected) so that was the assumption in Execute.

Umer Akram SSC-Insane Points: 22209 More actions · Answer 8

thanks for the question steve. brought some old memories back. 🙂

TomThomson SSC Guru Points: 104773 More actions · Answer 9

Nice question, but the tag is wrong; this isn't just powershell, it's a .NET framework method in several classes, available not only in powershell but also in C++, C#, VBS, F#, JavaScript, VB, .....

Some people claim it's badly named: we have ExecuteWithResults for batches where at least one result set is to be returned to the caller, so maybe the case where no result sets are to be returned should be ExecuteWithoutResults? But when I execute batch I expect that execution to have some results (eg a table is created or a row is deleted or inserted or a procedure is dropped) whether or not it returns me any result sets so ExecuteWithoutResults would be a terrible name, and ExecuteWithResults is also a bad name - why isn't it ExecuteWithResultSets?.

It's a pity that the result value -1 is used for three different things: (i) errors (ii) no rows were affected and (iii) no rowcounts were returned (ROWCOUNT ws off when any command affecting rows were executed). Maybe it can't reasonably be expected to distinguish (ii) from (iii) but it certainly ought to be able to tell whether there's been an error or not.

Tom

paul.knibbs SSCoach Points: 15320 More actions · Answer 10

TomThomson (12/11/2015)
It's a pity that the result value -1 is used for three different things: (i) errors (ii) no rows were affected and (iii) no rowcounts were returned (ROWCOUNT ws off when any command affecting rows were executed). Maybe it can't reasonably be expected to distinguish (ii) from (iii) but it certainly ought to be able to tell whether there's been an error or not.

I think that's down to the .NET philosophy that you should throw an exception rather than return an error value. In this case it ought to throw a SqlException when this happens, but the documentation says this only happens when "An exception occurred while executing the command against a locked row" or "a timeout occurred during a streaming operation", which seems a bit pointless really.

Ed Wagner SSC Guru Points: 287026 More actions · Answer 11

I don't even use PowerShell and got it without doing research.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

patricklambin SSCrazy Eights Points: 9994 More actions · Answer 12

Thanks for this nice question.

I have been able to answer it only because , as I am more a C# programmer , I have used it as a method of the classes SqlConnection and ServerConnection ( SMO is my favorite part ).