SQL Server stored procedure line number issue

tags:

views:

185

answers:

1

+2 Q:

SQL Server stored procedure line number issue

Hello everyone,

I am using SQL Server 2008 Enterprise. I met with issue which says line 9 of stored procedure foo is meeting with dead lock issue. My question is how to find exactly the 9th line of the stored procedure?

My confusion is because of coding format issue, how to locate 9th line correctly.

thanks in advance, George

+1 A:

It's the 9th line from the CREATE PROCEDURE statement. A SQL statement is often multiline so "line 9" will refer to the first line of the statement (eg INSERT or UPDATE)

However, if you have comments above the CREATE PROCEDURE or blank lines before it then you can't rely on this... so run ALTER PROC with ALTER PROC as first line in the batch.

gbn 2010-06-01 05:02:17

1. So, the most reliable way is to use alter proc from SQL Server Management Studio, then count the line number? 2. Shall I remove any comments above the store procedure from alter proc code generated by SQL Server Management Studio?

George2 2010-06-01 05:18:09

My confusion is about whether lines above alter procedure will be counted as line number count -- "SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO", and also how to treat multi-line statement in store procedure to deal with line number count?

George2 2010-06-01 05:29:59

It's line XX from the CREATE PROC

gbn 2010-06-01 06:36:18

Including such lines "SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO"? I think such line are created by SQL Server automatically, not created by the developed who writes store procedure?

George2 2010-06-01 06:47:45

Line 1 = "CREATE PROC". Ignore the SET statements

gbn 2010-06-01 07:52:31

You mean the create proc scripts generated from SSMS --> select a store procedure --> Create Store Procedure?

George2 2010-06-01 08:25:31

Yes............

gbn 2010-06-01 08:31:17

Why you prefer to use create then alter?

George2 2010-06-01 08:47:55

And how will the line be counted if there is long statement which will span for multiple lines in SSMS query editor?

George2 2010-06-01 08:48:36

Line number = first line...

gbn 2010-06-01 08:52:01

What is your answer to this question -- "Why you prefer to use create then alter"? :-)

George2 2010-06-01 08:54:05

CREATE or ALTER does not matter. It depends if you script for create or to modify. As long as CREATE or ALTER if first.

gbn 2010-06-01 09:03:24

I have tried another option, which will us Go to Management Studio>> Tools >> Options >>, then in tree view open Text Editor >> All Language >> line number option, and I think this option will falsely treat line number, since long statement which crosses multiple lines will be treated as multiple lines. Am I correct? Any comments?

George2 2010-06-01 09:13:09

No idea, but it's he script/edit line numbers not the line numbers parsed at execution time.

gbn 2010-06-01 09:28:14

Cool, question answered!

George2 2010-06-01 09:30:15

ansaurus

tags:

views:

answers:

SQL Server stored procedure line number issue

related questions