How to check Null in a select @Local_variable

Asked

Viewed 1,290 times

2

To set a default value for a local variable, if the select that should "set" it returns NULL? For example:

DECLARE @Minha_Var VARCHAR(70)
SELECT @Minha_Var = Nome FROM CLIENTES WHERE Id = 10


This query can return NULL, right? How to set a default value if this happens?

sql sql-server select null

  • Ricardo, posted working.

  • @Raonibz Thank you, it worked perfectly

  • 1

    Change for SET the variable @Minha_Vardoes not assign NULL if this is the return and you already have a value before. See: When to use SET and SELECT?

  • 1

    @Marconi Thanks for the tip. By the way, great didactics in the post reply.

2 answers

3


You can use CASE:

DECLARE @Minha_Var VARCHAR(70);

SELECT @Minha_Var = NOME
FROM CLIENTES 
WHERE ID = 10

SELECT (CASE @Minha_Var WHEN NULL THEN '1' ELSE '2' END) NOME


Você pode trazer diversos resultados:

SELECT (CASE @Minha_Var WHEN NULL THEN 'José' ELSE @Minha_Var END) NOME
SELECT (CASE @Minha_Var WHEN NULL THEN (SELECT NOME FROM CLIENTES WHERE ID = 1) ELSE @Minha_Var END) NOME

2

SQL SERVER has the function ISNULL and function COALESCE which makes your code much more elegant and clean.

That would be your code. 

declare @CLIENTES table
(
    id int,
    Nome varchar(100)
)

INSERT INTO @CLIENTES VALUES
(1, NULL),
(2, 'Jõao')

SELECT id, ISNUll(Nome, 'Meu Nome') as 'Com ISNUll', COALESCE(Nome, 'Meu Nome') as 'com COALESCE'
FROM @CLIENTES

DECLARE @Minha_Var VARCHAR(70)
SELECT @Minha_Var = ISNUll(Nome, 'Meu Nome') FROM @CLIENTES WHERE Id = 1
SELECT @Minha_Var = COALESCE(null, 'Meu Nome') FROM @CLIENTES WHERE Id = 1

SELECT  ISNULL(null, 'Meu Nome') AS Using_ISNULL
SELECT COALESCE(null, 'Meu Nome')  AS Using_ISNULL

Browser other questions tagged sql sql-server select null

You are not signed in. Login or sign up in order to post.