|
|
You're in the
sql server programming
group:SET NOCOUNT ON OPTION
sql server programming:
I know what it does but not exactly where and when I want to use it.
Personally, I use it at the top of every stored procedure, always. I never
want the rowcounts returned... [quoted text, click to view] "Leon" <vnality@msn.com> wrote in message news:uZiEERYtEHA.412@TK2MSFTNGP14.phx.gbl... > When and when not to set SET NOCOUNT ON? > > I know what it does but not exactly where and when I want to use it. > >
But you always have to set it to NOCOUNT OFF after the action within the
store procedure right? [quoted text, click to view] "Adam Machanic" <amachanic@hotmail._removetoemail_.com> wrote in message news:OPl5RXYtEHA.2196@TK2MSFTNGP14.phx.gbl... > Personally, I use it at the top of every stored procedure, always. I > never > want the rowcounts returned... > > > "Leon" <vnality@msn.com> wrote in message > news:uZiEERYtEHA.412@TK2MSFTNGP14.phx.gbl... >> When and when not to set SET NOCOUNT ON? >> >> I know what it does but not exactly where and when I want to use it. >> >> > >
No, the SET NOCOUNT is specific to the procedure and is set at execution of
the procedure. [quoted text, click to view] "Leon" <vnality@msn.com> wrote in message news:ur62eAZtEHA.1400@TK2MSFTNGP11.phx.gbl... > But you always have to set it to NOCOUNT OFF after the action within the > store procedure right? > > "Adam Machanic" <amachanic@hotmail._removetoemail_.com> wrote in message > news:OPl5RXYtEHA.2196@TK2MSFTNGP14.phx.gbl... > > Personally, I use it at the top of every stored procedure, always. I > > never > > want the rowcounts returned... > > > > > > "Leon" <vnality@msn.com> wrote in message > > news:uZiEERYtEHA.412@TK2MSFTNGP14.phx.gbl... > >> When and when not to set SET NOCOUNT ON? > >> > >> I know what it does but not exactly where and when I want to use it. > >> > >> > > > > > >
[quoted text, click to view]
> Therefore, you need to have the NOCOUNT set to OFF Can you show an example where the NOCOUNT setting affects @@ROWCOUNT? Like Adam, I use SET NOCOUNT ON in every single procedure I write, and I have never found a case where I had to set it OFF at any point in the procedure.
[quoted text, click to view] "Philippe [MS]" <ptrotin@online.microsoft.com> wrote in message news:uPgqYjbtEHA.3460@TK2MSFTNGP15.phx.gbl... > > UPDATE ... > IF @@ROWCOUNT = 0 > BEGIN > INSERT ... > END > > Therefore, you need to have the NOCOUNT set to OFF Phil, @@ROWCOUNT and the NOCOUNT option are mutually exclusive: CREATE TABLE #abc(def INT) SET NOCOUNT ON INSERT #abc (def) VALUES (1) SELECT @@ROWCOUNT
You set that value to ON when you do not want to see the row count number
after an insert or update operation. If your store procedure just SELECT things, do not hesitate to put SET NOCOUNT ON at the begining of the store procedure. If you want for example to create a store procedure that make an insert if the line does not exist and an insert if not, you can do : UPDATE ... IF @@ROWCOUNT = 0 BEGIN INSERT ... END Therefore, you need to have the NOCOUNT set to OFF Phil. [quoted text, click to view] "Derrick Leggett" <derrickleggett@yahoo.com> wrote in message news:uc40yFZtEHA.2196@TK2MSFTNGP14.phx.gbl... > No, the SET NOCOUNT is specific to the procedure and is set at execution of > the procedure. > > "Leon" <vnality@msn.com> wrote in message > news:ur62eAZtEHA.1400@TK2MSFTNGP11.phx.gbl... > > But you always have to set it to NOCOUNT OFF after the action within the > > store procedure right? > > > > "Adam Machanic" <amachanic@hotmail._removetoemail_.com> wrote in message > > news:OPl5RXYtEHA.2196@TK2MSFTNGP14.phx.gbl... > > > Personally, I use it at the top of every stored procedure, always. I > > > never > > > want the rowcounts returned... > > > > > > > > > "Leon" <vnality@msn.com> wrote in message > > > news:uZiEERYtEHA.412@TK2MSFTNGP14.phx.gbl... > > >> When and when not to set SET NOCOUNT ON? > > >> > > >> I know what it does but not exactly where and when I want to use it. > > >> > > >> > > > > > > > > > > > >
Setting NOCOUNT ON or OFF has absolutely no effect on @@ROWCOUNT.
Try Adam's little script and you'll see. [quoted text, click to view] "Philippe [MS]" <ptrotin@online.microsoft.com> wrote in message news:eTc7OigtEHA.220@TK2MSFTNGP15.phx.gbl... > It's because you never check the number of rows that are impacted by your > update or insert. > > Phil. > > "Aaron [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message > news:u4R7tydtEHA.2072@tk2msftngp13.phx.gbl... > > > Therefore, you need to have the NOCOUNT set to OFF > > > > Can you show an example where the NOCOUNT setting affects @@ROWCOUNT? > Like > > Adam, I use SET NOCOUNT ON in every single procedure I write, and I have > > never found a case where I had to set it OFF at any point in the > procedure. > > > > > >
[quoted text, click to view]
> It's because you never check the number of rows that are impacted by your > update or insert. I'm sorry, but you're mistaken. Adam's repro certainly checks for the number of rows impacted, and it also certainly works. Can you provide something concrete that backs up your claim? In the meantime, recall that SET NOCOUNT ON does not prevent the database from paying attention to what it's doing to the table(s). It merely suppresses that output from being displayed to the user or sent back in the stream. This will all become very clear if you spend thirty seconds in Books Online: Go / URL / tsqlref.chm::/ts_set-set_3ed0.htm "The @@ROWCOUNT function is updated even when SET NOCOUNT is ON."
That is just not true. SET NOCOUNT has no effect what so ever on
@@ROWCOUNT. -- Andrew J. Kelly SQL MVP [quoted text, click to view] "Philippe [MS]" <ptrotin@online.microsoft.com> wrote in message news:eTc7OigtEHA.220@TK2MSFTNGP15.phx.gbl... > It's because you never check the number of rows that are impacted by your > update or insert. > > Phil. > > "Aaron [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message > news:u4R7tydtEHA.2072@tk2msftngp13.phx.gbl... >> > Therefore, you need to have the NOCOUNT set to OFF >> >> Can you show an example where the NOCOUNT setting affects @@ROWCOUNT? > Like >> Adam, I use SET NOCOUNT ON in every single procedure I write, and I have >> never found a case where I had to set it OFF at any point in the > procedure. >> >> > >
It's because you never check the number of rows that are impacted by your
update or insert. Phil. [quoted text, click to view] "Aaron [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message news:u4R7tydtEHA.2072@tk2msftngp13.phx.gbl... > > Therefore, you need to have the NOCOUNT set to OFF > > Can you show an example where the NOCOUNT setting affects @@ROWCOUNT? Like > Adam, I use SET NOCOUNT ON in every single procedure I write, and I have > never found a case where I had to set it OFF at any point in the procedure. > >
Sorry, I write too fast without testing.
It's not valid for @@ROWCOUNT (Cf. SQL Docs << SET NOCOUNT >>). If you launch a proc / sql from Query analyzer, you see the impact of ON or OFF in the message table. Sorry again. :-( Phil. [quoted text, click to view] "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message news:ewQgF4gtEHA.3872@TK2MSFTNGP15.phx.gbl... > That is just not true. SET NOCOUNT has no effect what so ever on > @@ROWCOUNT. > > -- > Andrew J. Kelly SQL MVP > > > "Philippe [MS]" <ptrotin@online.microsoft.com> wrote in message > news:eTc7OigtEHA.220@TK2MSFTNGP15.phx.gbl... > > It's because you never check the number of rows that are impacted by your > > update or insert. > > > > Phil. > > > > "Aaron [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message > > news:u4R7tydtEHA.2072@tk2msftngp13.phx.gbl... > >> > Therefore, you need to have the NOCOUNT set to OFF > >> > >> Can you show an example where the NOCOUNT setting affects @@ROWCOUNT? > > Like > >> Adam, I use SET NOCOUNT ON in every single procedure I write, and I have > >> never found a case where I had to set it OFF at any point in the > > procedure. > >> > >> > > > > > >
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
| home · blog · groups | Questions? Comments? Contact the dn |