What will this query return?

Question

What will this query return?

Shiva N

Hall of Fame

Points: 3125
More actions
September 3, 2014 at 9:56 pm

#298978

Comments posted to this topic are about the item What will this query return?
Thanks,
Shiva N
Database Consultant

Viewing 15 posts - 1 through 15 (of 34 total)

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

Vimal Lohani SSCommitted Points: 1650 More actions · Answer 1

Thanks for the question dear.

Vimal LohaniSQL DBA | MCP (70-461,70-462)==============================The greatest barrier to success is the fear of failure ** Success is a journey not a destination**Think before you print, SAVE TREES, Protect Mother Nature

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 2

Hardy21

SSCrazy Eights

Points: 9708

September 4, 2014 at 12:15 am

#1743089

Nice one, thanks for sharing.

Thanks

Koen Verbeeck SSC Guru Points: 259165 More actions · Answer 3

Nice one, thanks.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

pmadhavapeddi22 SSCertifiable Points: 5306 More actions · Answer 4

When I work with CTE, I get this error when I dont give smicolon, so it was easy to answer 🙂

Thanks for the question

Carlo Romagnano SSC-Insane Points: 22752 More actions · Answer 5

pmadhavapeddi22 (9/4/2014)
When I work with CTE, I get this error when I dont give smicolon, so it was easy to answer 🙂
Thanks for the question

+1

Raghavendra Mudugal SSChampion Points: 10658 More actions · Answer 6

Thank you for the post, Shiva, good one.

ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

Raghavendra Mudugal SSChampion Points: 10658 More actions · Answer 7

Carlo Romagnano (9/4/2014)
pmadhavapeddi22 (9/4/2014)
When I work with CTE, I get this error when I dont give smicolon, so it was easy to answer 🙂
Thanks for the question
+1

+1

ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

DrKiller Right there with Babe Points: 780 More actions · Answer 8

I think the explanation need some work.

CTEs doesn't ALWAYS need a semi-colon in front.

This for example works fine:

DECLARE @tmpVariable INT

WITH cte

AS

(

SELECT 1/0 as 'Result'

)

SELECT *

FROM cte

I'd like to know when a CTE needs semi-colon and not... I always though its only after a select from a table, but apparently not 🙂

Umer Akram SSC-Insane Points: 22209 More actions · Answer 9

DrKiller (9/4/2014)
I think the explanation need some work.
CTEs doesn't ALWAYS need a semi-colon in front.
This for example works fine:
DECLARE @tmpVariable INT
WITH cte
AS
(
SELECT 1/0 as 'Result'
)
SELECT *
FROM cte
I'd like to know when a CTE needs semi-colon and not... I always though its only after a select from a table, but apparently not 🙂

+1, Nice question thou. Thanks for sharing

Raghavendra Mudugal SSChampion Points: 10658 More actions · Answer 10

twin.devil (9/4/2014)
DrKiller (9/4/2014)
I think the explanation need some work.
CTEs doesn't ALWAYS need a semi-colon in front.
This for example works fine:
DECLARE @tmpVariable INT
WITH cte
AS
(
SELECT 1/0 as 'Result'
)
SELECT *
FROM cte
I'd like to know when a CTE needs semi-colon and not... I always though its only after a select from a table, but apparently not 🙂
+1, Nice question thou. Thanks for sharing

In the local help it says "When a CTE is used in a statement that is part of a batch, the statement before it must be followed by a semicolon."

but the linked article states this

I know that is not a requirement to terminate every SQL Statement, but it does make it easier to read. However in most cases, other than readability the termination of the previous statement really doesn’t matter. . . In most cases.

I never really tested this much, but in general I just follow the statement highlighted in bold.

ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

Hany Helmy SSChampion Points: 13921 More actions · Answer 11

Easy mozo 🙂

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

PRAMANA.DBA SSCertifiable Points: 5507 More actions · Answer 12

PRAMANA.DBA

SSCertifiable

Points: 5507

September 4, 2014 at 5:19 am

#1743142

Hany Helmy (9/4/2014)
Easy mozo 🙂

SqlMel SSCrazy Points: 2901 More actions · Answer 13

Thanks for the question.

I'm actually surprised so many people have got this wrong.

I say that because when I started using CTEs I was not accostumed to using the semicolon so I would ALWAYS get this error. I'm sure it happened to a lot of other folks when learning this feature.

---------------
Mel. 😎

dwhitley Say Hey Kid Points: 666 More actions · Answer 14

I guess I'm one of the few just getting the msg 8134 when I run this? Running on 2008.

Msg 8134, Level 16, State 1, Line 1

Divide by zero error encountered.