COUNT(*) Returns all rows ignoring nulls right?
I'm not sure what you mean by "ignoring nulls" here. It returns the number of rows irrespective of any NULL
s
SELECT COUNT(*)
FROM (VALUES (CAST(NULL AS INT)),
(CAST(NULL AS INT))) V(C)
Returns 2
.
Altering the above query to COUNT(C)
would return 0
as when using COUNT
with an expression other than *
only NOT NULL
values of that expression are counted.
Suppose the table in your question has the following source data
+---------+---------------+
| Name | MaritalStatus |
+---------+---------------+
| Albert | Single |
| Bob | Single |
| Charles | Single |
| David | Single |
| Edward | Married |
| Fred | Married |
| George | NULL |
+---------+---------------+
The query
SELECT MaritalStatus,
COUNT(*) AS CountResult
FROM T
GROUP BY MaritalStatus
Returns
+---------------+-------------+
| MaritalStatus | CountResult |
+---------------+-------------+
| Single | 4 |
| Married | 2 |
| NULL | 1 |
+---------------+-------------+
Hopefully it is obvious how that result relates to the original data.
What does COUNT(*) OVER()
do?
Adding that into the SELECT
list for the previous query produces
+---------------+-------------+-----------------+
| MaritalStatus | CountResult | CountOverResult |
+---------------+-------------+-----------------+
| Single | 4 | 3 |
| Married | 2 | 3 |
| NULL | 1 | 3 |
+---------------+-------------+-----------------+
Notice that the result set has 3 rows and CountOverResult is 3. This is not a coincidence.
The reason for this is because it logically operates on the result set after the GROUP BY
.
COUNT(*) OVER ()
is a windowed aggregate. The absence of any PARTITION BY
or ORDER BY
clause means that the window it operates on is the whole result set.
In the case of the query in your question the value of CountOverResult
is the same as the number of distinct MaritalStatus
values that exist in the base table because there is one row for each of these in the grouped result.
You're trying to use a variable in places where you can't - as a part of the name of a trigger, and as the name of a table. Instead of passing @triggertable
in as a parameter, you may as well just concatenate it directly into the SQL statement, replacing:
@triggertable
with:
' + @triggertable + '
Then, you should get the results you want. Given that you're getting the table names from system tables, you shouldn't actually have to worry about SQL injection here. Do double-check how the cursor returns the table name - in some cases, it may return:
[tablename]
instead of just:
tablename
As noted by Kenneth Fisher, try printing @sql
instead of executing it to confirm it looks like what you want (since we've eliminated the parameters you were passing into sp_executesql
).
Best Answer
Returns
0
&
is the Bitwise AND operator.In your case
@T & @F
resolves to1 & 0
and so returns a result of datatypeBIT
with value0
When passed to the
PRINT
operator thisbit
result is implicitly cast to string and the result output to the client.Has quite a lot wrong with it.
AND
bit
is not the same as boolean. They are not interchangeable and SQL Server won't implicitly castbit
to a boolean datatype when needed (SQL Server does not implement the SQL Boolean datatype.).So you would need to use an expression like
instead of
Even then your problems aren't over -
PRINT
doesn't accept a boolean expression anyway. You could use the expression inCASE
as below.