SQL Server 2008 Management Studio Not Checking the Syntax of My Query

sql server 2008 management studio not checking the syntax of my query

It is taking the value of hs_id from the outer query.

It is perfectly valid to have a query that doesn't project any columns from the selected table in its select list.

For example

select 10 from HotelSupplier where id = 142

would return a result set with as many rows as matched the where clause and the value 10 for all rows.

Unqualified column references are resolved from the closest scope outwards so this just gets treated as a correlated sub query.

The result of this query will be to delete all rows from Photo where hs_id is not null as long as HotelSupplier has at least one row where id = 142 (and so the subquery returns at least one row)

It might be a bit clearer if you consider what the effect of this is

delete from Photo  where Photo.hs_id  in (select Photo.hs_id)

This is of course equivalent to

delete from Photo where Photo.hs_id = Photo.hs_id

By the way this is far and away the most common "bug" that I personally have seen erroneously reported on Microsoft Connect. Erland Sommarskog includes it in his wishlist for SET STRICT_CHECKS ON

How do I validate SQL syntax in SQL Server 2008 R2?

You can click the Parse query button in Management Studio. It's the blue check mark on the toolbar (you can also use Ctrl + F5):

parse.png

This only validates syntax, and doesn't check that the objects you've referenced exist, that joins are valid, etc. For example the following parses correctly since deferred resolution assumes that by the time you run the query "for real" the object will exist:

SELECT foo FROM dbo.table_does_not_exist;

This also passes parsing:

SELECT d.foo 
FROM x.dbo.does_not_exist AS d
INNER JOIN sys.objects AS s
ON d.blat = s.bar;

Even though sys.objects exists but does not contain the column bar.

It is essentially the same mechanism that allows you to compile a stored procedure that references objects that don't exist yet (which of course will fail at runtime).

Syntax for CTE in Microsoft SQL Server Management Studio

You're missing the SELECT (or UPDATE, DELETE or INSERT) that uses the CTE. There's no sense on declaring a CTE that won't be used in the statement.

WITH 
cte_REFERRALS_REPORTS (referralnum, refer_from, refer_from_name, refer_from_id, refer_to, refer_to_name, refer_to_id)
AS
(
SELECT
referralnum, refer_from,
ISNULL( RdicF.refname, PdicF.provname) END AS refer_from_name, --Code shortened, but you could keep the original for clarity.
refer_from_id, refer_to,
CASE
WHEN refer_to_id = 'R' THEN RdicT.refname
WHEN refer_to_id = 'P' THEN PdicT.provname END AS refer_to_name,
refer_to_id
FROM referral_t r
LEFT JOIN refcode_t RdicF ON r.refer_from = RdicF.refcode AND r.refer_from_id = 'R'
LEFT JOIN refcode_t RdicT ON r.refer_to = RdicT.refcode AND r.refer_to_id = 'R'
LEFT JOIN provcode_t PdicF ON r.refer_from = PdicF.provcode AND r.refer_from_id = 'P'
LEFT JOIN provcode_t PdicT ON r.refer_to = PdicT.provcode AND r.refer_to_id = 'P'
)
SELECT *
FROM cte_REFERRALS_REPORTS;

I made some changes to your code that might help developers and the optimizer to understand what's happening on those JOINS. You also need to understand that the semicolon (;) is a statement terminator and should be at the end of each statement and not at the beginning of a CTE.

SQL Server query weird behaviour

I think these two articles answer your question.

https://connect.microsoft.com/SQLServer/feedback/details/542289/subquery-with-error-does-not-cause-outer-select-to-fail

http://support.microsoft.com/kb/298674

This is expected behavior because your column name is not bound to a table. Therefore, if it can be resolved in the outer table (which in your query's case, it can), then the subquery doesn't fail. If you specify the table BillingData.customer_Id, you will get a failure. The articles say to follow this practice to avoid ambiguity.

SQL Server 2008 Query Editor changes the query logic

I tried to replicate this in the Query Designer and had a slightly different result. I typed the same as you:

SELECT * FROM Tab1 WHERE A='1' AND (B='1' OR C='1');

And got this:

SELECT     *
FROM Tab1
WHERE (A = '1') AND (B = '1') OR
(A = '1') AND (C = '1')

I have to say that the result is the same, but we can all see a dangerous road here. Also, I did not like the (A = '1') replication. Heck, I want the code how I coded it!

A word to the wise: I never format my queries in SQL Server Management Studio. Have you seen what it does to your view's code? I hate it. I just code somewhere else and paste in SMS when done.

Why can't I query OFFSET/ FETCH query on my SQL Server?

OFFSET FETCH is a new feature added to Sql Server 2012 and does not exist in Sql Server 2008.

Why is SQL Server not catching this? Typo in subquery causes accidental delete

The columns from the table declared in the outer query are available in the inner query. That's the same logic that comes into play when you build a correlated subquery.

So in this condition:

WHERE id1 = (SELECT id1 FROM #a2 WHERE id2=1)

The subquery returns as many recods as there are in table #a2, with id1 coming from the outer query. As long as there is a unique record #a2 that satisfies condition id2 = 1, this is actually equivalent to WHERE id1 = id1.

If you had prefixed the column names with table aliases, you would have received an error:

DELETE FROM #a1 t1 WHERE t1.id1 = (SELECT t2.id1 FROM #a2 t2 WHERE t2.id2 = 1);

SQL Server 2008 subquery with error. Still works the main query

Though your question doesn't show this (please post your actual code in future) Table1 must have a column called IDMispelledRecord.

Your subquery is referencing that column from the outer query.



Related Topics



Leave a reply



Submit