Procedure clarification
-
create procedure newproc as
select @@procid as procid
go
exec newproc
go
The above procedure of returning the procedure id returns the procedure id, where as the same procedure sans the go statement doesn't seem to work. The error message it throws is:
"Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'newproc'. The stored procedure will still be created."
Can someone explain why this happens.
Regards
Arun
-
With the 'go' statement you create the proc first then execute proc.
Without the 'go' statement you create the proc with a nested proc within. In this case you're self looping !!!
The error message (more like a warning) regards the nested proc has been created but it will continue create the proc.
-
As per the ever-friendly, ever-reliable Books Online,
Signals the end of a batch of Transact-SQL statements to the Microsoft® SQL Server™ utilities.
So when you excluded the first GO statement, all the statements were being submitted as one batch. This of course caused the warning message about the missing object. After executing the text and getting the error, if you look at the resulting stored procedure text you'll see it actually includes the EXEC newproc statement, because that line was part of the batch of statements submitted to the server.
--------------------
Colt 45 - the original point and click interface
-
guys do u mean to say whatever is included in the go statement would be the first one to get executed...do let me know if my understanding is correct.
i'm a newbie put things simpler...until i pick up things
Phil - i didn't exclude the first go statement rather i tried without include the go statement at all.
Cheers
Arun
-
What ever comes after the CREATE PROCEDURE and before the GO is treated as the text of the stored procedure. This is the same for all the other CREATE ... statements as well.
Where are you typing the SQL statements to test this? In Query Analyzer?
--------------------
Colt 45 - the original point and click interface -
As per every1's advice i'm using the query analyzer...they have asked me to abstain from Enterprise manager.
one more finding is on execution of the previous procedure the output was "1509580416" but on inclusion of another GO statement on the top of the procedure statement the output was different...(The concerning point is not the output but the working).
Hope i have answered for u'r queries...
3 Cheers
Arun
-
Ok, in the Object Browser for Query Analyzer ( F5 ), navigate down the tree till you find your stored procedure. ( You might need to refresh the list before it shows up ). Once you've located it, you can right-click it and script it to a new window. This will show you the resulting stored procedure text.
Also, you would get different ID numbers because you're creating a different procedure.
--------------------
Colt 45 - the original point and click interface -
I did as per your directions...and there i notice odd number of GO statements, don't they need to complement each other, one signals the start and the other signals the end.
To put it simpler - if my statements are included in the GO, are those statements are the one which get executed first. (This is question correct me if i'm wrong)
oops phill thanks for the patience - hope i'm bothering much on this simple issue...(doesn't stops here still lots 2 follow)
cheers
Arun
-
As per my earlier post "GO signals the end of a batch".
Did you run the statements both with and without the GO and see the results?
--------------------
Colt 45 - the original point and click interface -
i found my procedure listed under object browser...but when script object to new window and saved it, and when modified everytime it took the help of query analyzer to drop the procedure so that i execute the procedure with different positions of GO.
Find the script to my object code as follows:
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO --1 (When deleted this go it gave an error message)
create procedure myprocedure as
select @@procid as procid
go
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
For the rest i got the same result procid is 0
Now coming to the query analyzer...
create procedure myprocedure as
--1.go
select @@procid as procid
go
exec myprocedure
--go
drop procedure myprocedure
I get different procid for different combination of go. Do let me know have i followed the steps that you have told me.
Cheers
Arun
-
Think of GO as a way to combine T-SQL that would otherwise fail due to dependancies. It is also required to denote the end of a CREATE statement, if you have traling code in the same script, which is what you posted.
You get different @@PROCID values each time you create the stored procedure as it is running under a different PROCID. Change your stored procedure to output a constant to eliminate the confusion.
create procedure myprocedure as
select 1 as One
GO -- is the logical end of a CREATE statement
-- allows the create to be processed,
-- otherwise these will fail as myprocedure does not exist
exec myprocedure
drop procedure myprocedure
--
--If you omit the above GO then this entire statement is within the myprocedure
GO
You also cannot comment the GO statement in QA.
Andy
-
Andy,
Thanks! I tried the above code...and found that without the GO statement the procedure was created and then when i manually executed the procedure, it gave an error after 32 iterations...with the output 1 row affected (could guess why it happened).
Thanks again...
3 Cheers
Arun
-
when you exec the proc, the proc executes itself (looping/nesting) 32 times.
It's only 32 iterations because a stored proce can only nested proc up to 32 times.
Cheers.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply