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

Question

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

Navigation

#1 by (1 votes)
#2 by (0 votes)
#3 by (0 votes)

0

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 Address table I have a "Complement" field that allows null values. However when my CTE does the query and compares with the @complemento if the complement is empty, the result of the CTE returns nothing, even the NULL value existing in the pendant_table, otherwise the reverse if it looks 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 because the subquery returns null value, this error happens. Note 2: The other procedure after the EXEC only registers addresses.

sql query null sql-server-2012

asked by anonymous 02.12.2015 / 05:05

3 answers

How to convert decimal to binary with large numbers? Null reference, I made a check before inserting the values and the error continues

score 1 · Answer 1

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

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

The NULL values can not be compared with the = operator.

In order for null lines to be 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,'') [...]

score 0 · Answer 2

Use IDENTITY_SCOPE to get 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 constraint, use cascateamneto to make the CRUD complete.

score 0 · Answer 3

The error that appears in print , is because the query "(SELECT ID_Endereco FROM CTE_ENDERECO)" did not bring any results, ie NULL, and you tried to assign this result to the "FK_Endereco" "(which does not allow null).

The print error could be solved as follows:

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

Switch to your query on the insert line:

The value:

(SELECT ID_Endereco FROM CTE_ENDERECO)

By 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 mentioned error with the comparison of the value of the "complement" column with the variable "@complemento". This comparison you said does not appear on the prints screen.

If the problem is when giving the select in the field "complement" 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