Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Working on stored procedures


Working on stored procedures

Author
Message
satisrajak
satisrajak
Valued Member
Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)

Group: General Forum Members
Points: 55 Visits: 22
Comments posted to this topic are about the item Working on stored procedures
bitbucket-25253
bitbucket-25253
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6159 Visits: 25280
Nice basic question to start the week... Thanks

Am suprised / astonished at the number of individuals who missed this fundamental question.


Correct answers: 27% (4)
Incorrect answers: 73% (11)
Total attempts: 15



If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
sam.dahl
sam.dahl
Old Hand
Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)

Group: General Forum Members
Points: 392 Visits: 887
Firstly, "I have two stored procedures" suggested to me that the scripts we were executing were the actual stored procedures not necessarily the create scripts depicted.
This may well be a terminology deficiency on my part, I resorted to flipping a coin as to which way I took the question.

Secondly, I would have been wrong in any case because I tested this in SQL Server 2012 (since no version was specified I considered this valid):
Both create scripts execute successfully and both stored procedure scripts fail when actually executed (obviously).

EDIT: spelink.
Lokesh Vij
Lokesh Vij
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1720 Visits: 1599
bitbucket-25253 (8/26/2012)
Nice basic question to start the week... Thanks

Am suprised / astonished at the number of individuals who missed this fundamental question.


Correct answers: 27% (4)
Incorrect answers: 73% (11)
Total attempts: 15



I am also astonished to see this w00t

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com

Follow me @Twitter


Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18589 Visits: 13248
Nice question to start the week.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8762 Visits: 11718
sam.dahl (8/26/2012)
Firstly, "I have two stored procedures" suggested to me that the scripts we were executing were the actual stored procedures not necessarily the create scripts depicted.
This may well be a terminology deficiency on my part, I resorted to flipping a coin as to which way I took the question.

I agree that this is not entirely linguistically correct, but I tend to disregard linguistic errors if the intent is obvious. In this case, after reading the actual question ("If I execute both scripts seperately") and the answer options, the intent of the question was obvious.

Secondly, I would have been wrong in any case because I tested this in SQL Server 2012 (since no version was specified I considered this valid):
Both create scripts execute successfully and both stored procedure scripts fail when actually executed (obviously).

After reading this, I tested it for myself on my SQL 2012. I ensured no table named table1 or table2 exists, then executed this batch:
create table table2 (col1 int);
go
create proc sp_test1
as
begin
select col1, col2
from table1
end
go
create proc sp_test2
as
begin
select col1, col2
from table2
end
go


The result was as I expected - sp_test1 was created and the attempt to create sp_test2 failed with the "invalid column name" error message.

Are you sure you didn't overlook something when testing this on your database?


Finally, a remark for everyone - prefixing names of stored procedures with sp_ is a very bad habit. This prefix is reserved for system stored procedures; the name resolution works slightly differently for these proces. This makes them a bit slower. More important - if Microsoft decides to include a stored proc with the same name in the next release (or even service pack!), your code will stop working, because you will now invoke the Microsoft version of the stored proc!
If you really must prefix your stored procedures (a habit which I personally find annoying and pointless), then use a different prefix. I believe usp_ is quite common. (But better is not to prefix stored procedures at all.)


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Erav
Erav
SSC Veteran
SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)

Group: General Forum Members
Points: 205 Visits: 168
Nice basic question but you have to read question carefully.:-)
Divine Flame
Divine Flame
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1713 Visits: 2813
Nice question! thanks.

sam.dahl (8/26/2012)
Secondly, I would have been wrong in any case because I tested this in SQL Server 2012 (since no version was specified I considered this valid):
Both create scripts execute successfully and both stored procedure scripts fail when actually executed (obviously).



I believe that you had copied & executed the script provided in the question. These scripts are referring to tables table1 & table2. To test the scripts you should first create the table2 in your database (as the question specifies). Otherwise both table1 & table2 will not be existing in the database & both scripts will run successfully.


Sujeet Singh
demonfox
demonfox
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1311 Visits: 1192
nice question ;
one more observation :

-- t1(id int,data varchar(50)) ; t6 table doesn't exist
create procedure usp_testing
as
select a.id,a.testing from t1 a
inner join t6 b
on b.id = a.id
go


this doesn't throw any error either, although column doesn't exist in the t1.

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one Ermm
(Bob Brown)
(Bob Brown)
SSC Eights!
SSC Eights! (849 reputation)SSC Eights! (849 reputation)SSC Eights! (849 reputation)SSC Eights! (849 reputation)SSC Eights! (849 reputation)SSC Eights! (849 reputation)SSC Eights! (849 reputation)SSC Eights! (849 reputation)

Group: General Forum Members
Points: 849 Visits: 1145
Nice clear question. Good start to the week.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search