SQL Clone
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 (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)

Group: General Forum Members
Points: 61 Visits: 22
Comments posted to this topic are about the item Working on stored procedures
bitbucket-25253
bitbucket-25253
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26663 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
UDP Broadcaster
UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)

Group: General Forum Members
Points: 1446 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
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4742 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
SSC Guru
SSC Guru (110K reputation)SSC Guru (110K reputation)SSC Guru (110K reputation)SSC Guru (110K reputation)SSC Guru (110K reputation)SSC Guru (110K reputation)SSC Guru (110K reputation)SSC Guru (110K reputation)

Group: General Forum Members
Points: 110556 Visits: 13338
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
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27088 Visits: 12722
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
Old Hand
Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)

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

Group: General Forum Members
Points: 7335 Visits: 2816
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
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2975 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)
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1223 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