SQL Server subquery is always returning null. How to fix.

Question

I have a Procedure and a COMMON TABLE EXPRESSION(subquery). This my CTE does a search and offers to my query the result, my query then uses the results of it.

In my table Address I have a field "Complement" that allows null values. However when my CTE queries and compares with the @add-on if the add-on is empty, the CTE result returns nothing, even though the NULL value exists in the addressee table, otherwise, if you are looking for a record whose complement is filled the search finds.

I’ll show you the code and the error.

Note 1: The error happens because FK does not accept null values and as the subquery returns null value, this error happens. Note 2: The other process after EXEC only registers addresses.

Answer 1

When the complement column of the table, has the value NULL in a row, so that it is returned by the query it is not enough to do:

[...] where complemento = @complemento [...]

NULL values cannot be compared with the operator =.

So that the lines to NULL are returned, it can be done like this:

[...] where complemento = @complemento or (complemento is null and @complemento is null) [...]

Or so:

[...] where isnull(complemento,'') = isnull(@complemento,'') [...]

Answer 2

The error in print, is because the query "(SELECT Id_address FROM CTE_ENDERECO)" did not bring any result or NULL and you tried to assign this result to the "Fk_address" field of the table "Tag" (which does not allow null).

The error of print could be solved as follows:

DECLARE @FK_Endereco AS VARCHAR(10)
SET @FK_Endereco = (SELECT ID_Endereco FROM CTE_ENDERECO);

Change in your query, on the line of the Insert:

The value:

(SELECT ID_Endereco FROM CTE_ENDERECO)

For the value:

(CASE WHEN @FK_Endereco IS NULL OR @FK_Endereco = '' THEN 'Sem Endereço' ELSE @FK_Endereco END)

I did not understand the relation of the error cited with the comparison of the value of the "add-on" column with the variable "@add-on". This comparison you said does not appear in prints screen.

If the problem is giving the select in the "complement" field of the table "CTE_ENDERECO" just use:

SELECT (CASE WHEN complemento IS NULL THEN '' ELSE complemento END) AS complemtento FROM CTE_ENDERECO

Instead of giving a simple SELECT in the field that can be null (as you should be doing):

SELECT complemento FROM CTE_ENDERECO

Answer 3

Use IDENTITY_SCOPE to take the primary key of the last inserted record and add it to the other table. Depending on how your tables were created, if you put foreign key restriction, use cascateamneto to make the CRUD complete.