I seem to approach thinking about sql the wrong way. I am always writing things that do not work.
For example I need a variable. So i think:
DECLARE @CNT AS INT
SET @CNT = COUNT(DISTINCT database.schema.table.column)
Why doesn't this work...? I am using a fully qualified reference here, so the value I want should be clear.
DECLARE @CNT AS INT
SET @CNT = (SELECT COUNT(DISTINCT database.schema.table.column) FROM column)
This works... but why do I have to use select?
Does everything have to be prefaced with one of the DDL or DML statements?
Secondly: I can't debug line by line because a sql statement is treated all as one step. The only way I can debug is if I select the innermost sub-query and run that, then include next outer sub query and run that, and so on and so forth.
Is there a locals window?
I've heard about set-based thinking rather than iterative thinking, I guess I am still iterative even for functional languages... the iteration is just from innermost parentheses to outermost parentheses, and applied to the whole set. but even here I run into trouble because I don't know which value in the set causes the error.
Sorry if this seems scatterbrained... I guess that just kinda reflects how I feel about it. I don't know how to architect a big stored procedure from lots of little components......Like in vba I can just call another sub-routine and make sure the variables I need are global.
tldr: Need the conceptual grounding / knowing what actually happens when I type something and hit F5
On Question #1, You need select because that's how SQL works. You've given it a name, but haven't told it what to do with that name (select it, update it, delete it?) Just saying the column name is not grammatically correct.
On #2, Yes, SQL is declarative, you're not telling it what to do, you're telling it what to return. It will retrieve the data in the order that is most efficient at that particular moment in time, Normally your sub-query will be the last thing to run, not the first.