Working on stored procedures
-
Comments posted to this topic are about the item Working on stored procedures
-
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
-
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.
-
bitbucket-25253 (8/26/2012)
Nice basic question to start the week... ThanksAm 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[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
Nice question to start the week.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
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/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/ -
Nice basic question but you have to read question carefully.:-)
-
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 -
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: -
Nice clear question. Good start to the week.
-
Hugo Kornelis (8/27/2012)
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:
Part of the question states that "If I execute both scripts seperately", not both in a batch. Does this have a different effect?
-
(Bob Brown) (8/27/2012)
Hugo Kornelis (8/27/2012)
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:
Part of the question states that "If I execute both scripts seperately", not both in a batch. Does this have a different effect?
If you execute both CREATE PROC statement in a single batch, you get a syntax error. A CREATE PROC must always be a complete batch. That's why I added the "go" between the first and second procedure.
If you execute them one at a time, I get the exact results as described in the question and answer. Executing all batches at once has basically the same effect, but it suppresses the "Command(s) completed successfully" result. This result is not generated by SQL Server itself, but by Management Studio. (If you use a different client tool to execute this code, for instance osql.exe, you'll see that SQL Server simply remains silent after executing a statement that produces no output of its own).
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/ -
Nice question. Thanks.
I have in fact been bitten by this behaviour on occasion, so I could recognize the pattern of possibilities.
-
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.That was my interpretation as well. Bad question 😉
-
if you run the script as it is, it will fail because a GO is requried.
Secondly, I just ran this in a new database, and when creating the proc's separately both of them were created successfully. I am using sql server 2008r2. Anyone else had the same?
Viewing 15 posts - 1 through 15 (of 42 total)
You must be logged in to reply to this topic. Login to reply