In this
article, we will see how GO can have an effect on writing stored procedure,
before that go through my previous post
We will
directly jump into the example to see the effect of go in stored procedure, Now
we are creating the stored procedure MyManager, and if you notice after end
block of stored procedure, there is a select statement and a GO statement
proceed with it.
Create procedure MyManager
as
begin
select 'My Manager is the best one.'
end
select 'Oh come on! This is absolutly lie'
go
Command(s) completed successfully.
Now if we
try to execute the procedure..
exec MyManager
---------------------------
My Manager is the best one.
(1 row(s) affected)
---------------------------------
Oh come on! This is absolutly
lie
(1 row(s) affected)
Say, if you show this stored procedure’s output to your Manager, you
better have a new job lined up.
What happened? You intended to show him only the first line, but the
output of the second select also is displayed.
Why? Because the whole code is scripted into the syscomments table until a
GO command is found.
Now lets drop the procedure and recreate the same
drop proc MyManager
Command(s) completed
successfully.
Procedure lets recreate the same procedure, where select statement appears
after the GO statement.
Create procedure MyManager
as
begin
select 'My Manager is the best one.'
end
go
select 'Oh come on! This is absolutly lie'
Now if we
execute the procedure, we gets our desired result.
exec MyManager
---------------------------
My Manager is the best one.
(1 row(s) affected)
Conclusion: If the stored procedure isn’t
immediately followed by a GO command, an executable statement that appears
after the stored procedure will be executed together with the stored procedure,
which as we have seen can have unintended consequences.
I just like to code and be happy.
Post Reference: Vikram Aristocratic Elfin Share
No comments:
Post a Comment