How to add "SET NOCOUNT ON" in SP template? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to add "SET NOCOUNT ON" in SP template?

Author

Topic

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2009-07-23 : 09:27:43

Store procedure has a template of adding the following statement:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

How to add "SET NOCOUNT ON" into the template as below?

SET NOCOUNT ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

asgast
Posting Yak Master

149 Posts

Posted - 2009-07-23 : 10:18:30

I don't see the logic here

when you add
SET NO COUNT ON
GO
it will be applied only to our current session not to the procedure itself

and NO COUNT ON is already in the standard template

CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
-- Add the parameters for the stored procedure here
<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,
<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

or are we talking about different things here?

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2009-07-23 : 11:01:10

In SQL 2005, when you open new store procedure, one template will open the following text for you:

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
-- Add the parameters for the stored procedure here
<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,
<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO

asgast
Posting Yak Master

149 Posts

Posted - 2009-07-24 : 06:59:58

yes and no count is already there after BEGIN it will be in your procedure

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-24 : 07:47:08

In ssms main menu click on view and then "template explorer" (if not already shown)
In template explorer expand folder "Stored Procedure"
There you can see the templates
Right click on your wanted template and choose edit
Make your changes and click the save button.

That's all I think.

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources