Working on stored procedures

Question

Post reply

Working on stored procedures

Viewing 15 posts - 16 through 30 (of 42 total)

You must be logged in to reply to this topic. Login to reply

Error Handler Ten Centuries Points: 1086 More actions · Answer 1

Nice question to start the week! Good to polish basic....

Best,
Naseer Ahmad
SQL Server DBA

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 2

mandeep_nyc (8/27/2012)
if you run the script as it is, it will fail because a GO is requried.

That's why the question text explicitly says: "If I execute both scripts separately". You only need GO if you execute them as one; when you execute them one by one, GO is not required.

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?

That's why the question text explicitly says: "In the second procedure table2 and col1 exist, but col2 does not exists". In your empty database, table2 and col1 don't exist.

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/

Steve Jones - SSC Editor SSC Guru Points: 736068 More actions · Answer 3

kent_secher (8/27/2012)
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 😉

I think you are completely misreading this. You should re-evaluate how this reads since it seems rather clear.

Steve Jones - SSC Editor SSC Guru Points: 736068 More actions · Answer 4

mandeep_nyc (8/27/2012)
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?

Did you set up the tables correctly?

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 5

Thank you for such a back-to-basics question!

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA

Revenant SSC-Forever Points: 42467 More actions · Answer 6

Keld Laursen (VFL) (8/27/2012)
Nice question. Thanks.
I have in fact been bitten by this behaviour on occasion, so I could recognize the pattern of possibilities.

+1

EL Jerry SSCertifiable Points: 7063 More actions · Answer 7

Good back-to-basics question. I had to re-read the question twice to make sure I understood the purpose of the question.

"El" Jerry.

"A watt of Ottawa" - Gerardo Galvan

To better understand your help request, please follow these best practices.[/url]

BarbW SSCarpal Tunnel Points: 4759 More actions · Answer 8

I missed this one today, not paying close enough attention on a Monday - who'd have guessed! 😀

SQLRNNR SSC Guru Points: 281334 More actions · Answer 9

Thanks for the question

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Kenneth.Fisher SSC-Insane Points: 20396 More actions · Answer 10

demonfox (8/27/2012)
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.

Great question. Fun one for the beginning of the week.

demonfox: Awesome observation. I just had to play with it a bit myself. I tried the following scenarios:

CREATE TABLE table2 (col1 VARCHAR(10))

SP1

CREATE PROCEDURE usp_test1

AS

BEGIN

-- Query 1

SELECT table2.col2

FROM table2

ENDError as stated in the original QOD

SP2

CREATE PROCEDURE usp_test1

AS

BEGIN

-- Query 1

SELECT table2.col2

FROM table2

JOIN table1

ON table2.col1 = table1.col1

ENDQuery 1 has a reference to a missing table. There is no compile error. The stored procedure is created.

SP3

CREATE PROCEDURE usp_test1

AS

BEGIN

-- Query 1

SELECT table2.col2

FROM table2

-- Query 2

SELECT * FROM table1

ENDQuery 1 has no reference to a missing table. Even though there is a reference to the missing table in Query 2 it gives a compile error. SP not created.

SP4

CREATE PROCEDURE usp_test1

AS

BEGIN

-- Query 1

SELECT table2.col1

FROM table2

JOIN table1

ON table2.col1 = table1.col1

WHERE 1/0 = 'more than one problem with this query'

and cast('abcd' as int) = 1

ENDAnd even more interestingly even though the query has some obvious problems that should show up at compile time the stored procedure is created without an error.

It seems that if your query references a missing table the SP will be allowed regardless of what else is wrong with the query.

Now I only tested this in 2008 so it may react differently in later versions.

Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

Steve Jones - SSC Editor SSC Guru Points: 736068 More actions · Answer 11

Kenneth.Fisher (8/27/2012)
It seems that if your query references a missing table the SP will be allowed regardless of what else is wrong with the query.

This is deferred resolution and it is by design. It allows you to create objects out of order.

Kenneth.Fisher SSC-Insane Points: 20396 More actions · Answer 12

Steve Jones - SSC Editor (8/27/2012)
Kenneth.Fisher (8/27/2012)
It seems that if your query references a missing table the SP will be allowed regardless of what else is wrong with the query.
This is deferred resolution and it is by design. It allows you to create objects out of order.

Understood. Where I'm not sure I agree with the design is the fact that if there is a table in the query that doesn't exist all other errors seem to be ignored. I would think that syntax should be checked first, not object existence.

That being said I had understood the deferred resolution. I just hadn't followed it to this extent before.

Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

Lokesh Vij SSChampion Points: 10836 More actions · Answer 13

Kenneth.Fisher (8/27/2012)
demonfox (8/27/2012)
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.
Great question. Fun one for the beginning of the week.
demonfox: Awesome observation. I just had to play with it a bit myself. I tried the following scenarios:
CREATE TABLE table2 (col1 VARCHAR(10))
SP1
CREATE PROCEDURE usp_test1
AS
BEGIN
-- Query 1
SELECT table2.col2
FROM table2
ENDError as stated in the original QOD
SP2
CREATE PROCEDURE usp_test1
AS
BEGIN
-- Query 1
SELECT table2.col2
FROM table2
JOIN table1
ON table2.col1 = table1.col1
ENDQuery 1 has a reference to a missing table. There is no compile error. The stored procedure is created.
SP3
CREATE PROCEDURE usp_test1
AS
BEGIN
-- Query 1
SELECT table2.col2
FROM table2
-- Query 2
SELECT * FROM table1
ENDQuery 1 has no reference to a missing table. Even though there is a reference to the missing table in Query 2 it gives a compile error. SP not created.
SP4
CREATE PROCEDURE usp_test1
AS
BEGIN
-- Query 1
SELECT table2.col1
FROM table2
JOIN table1
ON table2.col1 = table1.col1
WHERE 1/0 = 'more than one problem with this query'
and cast('abcd' as int) = 1
ENDAnd even more interestingly even though the query has some obvious problems that should show up at compile time the stored procedure is created without an error.
It seems that if your query references a missing table the SP will be allowed regardless of what else is wrong with the query.
Now I only tested this in 2008 so it may react differently in later versions.

Awesome!

Kenneth.Fisher and DemonFox: It was real fun checking 'em out 🙂

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 14

Kenneth.Fisher (8/27/2012)
Understood. Where I'm not sure I agree with the design is the fact that if there is a table in the query that doesn't exist all other errors seem to be ignored. I would think that syntax should be checked first, not object existence.

That is in fact the exact order that is being used. The first step is parsing, where syntax errors are detected. Next step is binding (connecting names to objects). If this fails for a table in a query in a stored proc, deferred name resolution causes the error to be suppressed and the remaining steps to be postponed until first execution. After parsing comes optimizing (resulting in a query execution plan); only extreme edge cases can cause errors in this step.

Here is a simple query to prove that syntax errors show up even if you reference non-existant tables:

CREATE PROC WrongSyntax

AS

SLECT col1, col2

FROM table1;

go

The errors you had in your query, division by zero and conversion, are both run-time errors. Using constants instead of variables does not change that. That's why you can create and use a stored procedure such as the one below:

CREATE PROC ForceError

AS

DECLARE @a int = 1 / 0;

go

EXEC ForceError;

go

This can be useful in systems that require standard error handling (the procedure would need to be changed to accept variables with error message and code, and to take all required actions before forcing the error). I'd prefer to use THROW or RAISERROR, but I've seen this techique being used. And another situation where it can be very useful is in a query that does setbased actions with a builtin error checking - somewhere in the query, there would be a CASE WHEN (whatever signals en error) THEN 1/0 ELSE 0 END expression.

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/

Kenneth.Fisher SSC-Insane Points: 20396 More actions · Answer 15

Thanks for the clarification Hugo. Now that you describe it becomes a lot clearer. Still a very interesting effect. I'm guessing that the parser checks for the existence of the table first and then the existance of any columns which explains why a query that has both problems will still allow the SP to be created.

Thanks again

Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]