November 8, 2019 at 12:00 am
Comments posted to this topic are about the item The Derived Table
November 8, 2019 at 8:15 am
GO
CREATE procedure spt
as
SELECT C.CustomerID
FROM
(
SELECT
sh.CustomerID,
OrderDate = MAX(sh.OrderDate)
FROM dbo.SalesHeader AS sh
GROUP BY sh.CustomerID
) AS C
INNER JOIN C AS c2
ON C.CustomerID = c2.CustomerID;
GO
select * from sys.procedures p where p.name='spt'
GO
drop procedure if exists dbo.spt
GO
select * from sys.procedures p where p.name='spt'
November 8, 2019 at 8:32 am
This may sound like an extremely basic question but what's the difference between an execution error and a syntax error in this case? The message that I get suggests that it's an error in the execution of the code because there's an object not there and that's what fits the explanation. The word 'syntax' doesn't appear anywhere in the error message. If I alter the code slightly, I can generate a message that explicitly uses the word 'syntax'.
The explanation makes total sense and it's obvious why there'd be an error but it's not obvious how you'd tell the errors apart.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
November 8, 2019 at 8:49 am
GO
CREATE procedure spt
as
SELECT C.CustomerID
FROM
(
SELECT
sh.CustomerID,
OrderDate = MAX(sh.OrderDate)
FROM dbo.SalesHeader AS sh
GROUP BY sh.CustomerID
) AS C
INNER JOIN C AS c2
ON C.CustomerID = c2.CustomerID;
GO
This is a demonstration that the error is at execution time and it is NOT a syntax error.
November 8, 2019 at 10:39 am
If you have a dbo.C table/view in the database the query will run ok.
November 8, 2019 at 12:33 pm
Which would be further proof that its an execution error and not syntax.
November 8, 2019 at 2:55 pm
I have to agree this is an execution error and not a syntax error. Even simpler would be to Parse the query. That checks for syntax errors regardless of objects in the database. The syntax here is perfectly fine, it will however fail at run time with an invalid object name.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 8, 2019 at 3:27 pm
I do agree and apologies. Not sure what I was thinking when I wrote this one based on an issue. It is a gray area, as nothing has executed. This discovered during query plan build, not during the execution of the query, but it is clearly correct syntax.
November 8, 2019 at 3:48 pm
Not to worry Steve. We appreciate all the work you do for the site. And this QOTD was quite interesting.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 11, 2019 at 4:59 am
Nice question, thanks Steve
____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
November 11, 2019 at 6:48 am
I don't understand. When I answered this question and selected "syntax error" I was notified that my answer was incorrect and the correct answer was "execution error" instead. Now the correct answer is posted as "syntax error" but my answer is still incorrect? What did I miss?
LinkedIn: https://www.linkedin.com/in/sqlrv
Website: https://www.sqlrv.com
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply