1

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

ThomasRones
  • 657
  • 8
  • 29
  • 4
    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, 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, which may or may not mean running your inner SQL first. – JeffUK Jul 20 '17 at 13:07
  • 2
    SQL takes years of experience (or googling) to learn all the syntax for all of the functions. Even if it is "human readable" that doesn't mean you can write anything you want. There are still language rules that are enforced. – Jacob H Jul 20 '17 at 13:18
  • 1
    You do need to SELECT if you're getting field data. But if you're just getting it from a variable or a hardcode value then you can SET it without a using select. F.e. `set @Var1 = LEFT(@Var2,8);`. But COUNT is an aggregation function, and you always need a select with those. – LukStorms Jul 20 '17 at 13:18

2 Answers2

6

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.

JeffUK
  • 4,107
  • 2
  • 20
  • 34
0

Yes, you have to use SELECT in-order to fetch that data first and then assign it to variable. You can also do it like

DECLARE @CNT AS INT
SELECT @CNT = COUNT(DISTINCT `column`) FROM database.schema.table
Rahul
  • 76,197
  • 13
  • 71
  • 125