Nitpicky QA annoyance

Question

Post reply

Nitpicky QA annoyance

homebrew01

SSC Guru

Points: 55548
More actions
December 2, 2005 at 7:40 am

#364408

When editing Stored Procedures through QA, it adds a blank line at the beginning & end. Is there some way to prevent that from happening ?

Viewing 10 posts - 1 through 9 (of 9 total)

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

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 1

/*use GO */

GO

CREATE PROC TESTTEST

AS

SELECT 'TEST'

GO

sp_helptext TESTTEST

GO

DROP PROC TESTTEST

Regards,
gova

homebrew01 SSC Guru Points: 55548 More actions · Answer 2

homebrew01

SSC Guru

Points: 55548

December 2, 2005 at 7:49 am

#607352

Sorry ... I don't quite follow.

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 3

If a GO is used one blank line in the end is avoided. Check using results to grid. (Ctrl+D)

First this

/*use GO */

CREATE PROC TESTTEST

AS

SELECT 'TEST'

Go

sp_helptext TESTTEST

GO

DROP PROC TESTTEST

GO

Then this

Then This

GO

CREATE PROC TESTTEST

AS

SELECT 'TEST'

GO

sp_helptext TESTTEST

GO

DROP PROC TESTTEST

Regards,
gova

homebrew01 SSC Guru Points: 55548 More actions · Answer 4

Here's my SP in round 1 as seen through EM (maybe I'm missing something)

....................................

CREATE PROC TESTTEST

AS

SELECT 'TEST'

.....................................

Then I edit it through object browser in QA, and add a blank space in the SELECT line, & run the alter

.......................

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

ALTER PROC TESTTEST

AS

SELECT 'TEST' --- blank space added here

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

....................................

Next time I edit it, there's a new blank line. If a developer edits a SP 15 times before moving to production, there are a lot of blank lines, or they have to delete them all for cosmetic reasons.

<<-- NEW BLANK LINE ADDED BY QA

CREATE PROC TESTTEST

AS

SELECT 'TEST'

GO

<<-- NEW BLANK LINE ADDED BY QA

.................................

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 5

I tested executing several times I did not get the error. I am not sure now. May be some who had or has this problem can check into this.

I asked to add a GO since without a GO it adds a line in the end. But even if you alter the procedure for several times it will be always one blank line. Your problem looks like it is different.

This what I got after executing several times in QA

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TESTTEST]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[TESTTEST]

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

CREATE PROC TESTTEST

AS

SELECT 'TEST'

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

Regards,
gova

SQLBill SSC Guru Points: 51440 More actions · Answer 6

I use QA a lot to edit stored procedures and that has never happened to me.

What edition of SQL Server are you using? (6.5, 7, 2000, 2005)

What version (service pack/hotfix level) is your SQL Server?

-SQLBill

homebrew01 SSC Guru Points: 55548 More actions · Answer 7

We're on SQL2000, SP4

A developer pointed this out when I asked why there were so many blank lines at the beginning of her SP.

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 8

Are you using the Edit menu item of Object Browser in Query Analyzer? It doesn't really open the procedure for editing, it's just a shortcut for Script to New Window as Alter. The generated script does add a blank line before and after, I think it's part of the standard SET QUOTED IDENTIFIER header and footer it uses.

Query Analyzer does not add anything when you run the Alter Procedure command, but if you run the scripted version from the Object Browser without removing the extra blank lines you will be stuck with them.

homebrew01 SSC Guru Points: 55548 More actions · Answer 9

Scott, Yes, that's the scenario she's encountering ..... I guess we'll just have to remember to delete the lines if we edit them that way.