CODEWITHSUNDEEP
sqlsql-serverisnull
Jui Test
Jui Test

Reputation: 2409

how to apply isnull to variable?

I have table called sample with columns:

Id, Name, Dept, active

Query: 

select Id 
from Sample 
where Dept = @Dept and active = 1

I want to fetch id from sample table name by passing deptment name whose is active. There can come situation where where I get 2 records. Two dept might be active. That's why I am taking top 1. Some time might not come any record.

That's why I used like this in stored procedure:

declare @TempId int      

set top 1 @TempId  = Id 
from Sample 
where Dept = @Dept and active = 1

if(@TempId is null)
begin
    @TempId = 0
end

Can I use isnull in the above select instead of after which is suitable for both my conditions?

Upvotes: 1

Views: 1024

Answers (3)

Paweł Dyl
Paweł Dyl

Reputation: 9143

I would write following code:

DECLARE @TempId int =
    COALESCE((SELECT TOP 1 Id FROM [Sample] WHERE Dept = @dept AND Active=1), 0)

If no rows are returned, NULL coalescing function is used.

Upvotes: 0

Serg
Serg

Reputation: 22811

No. First it must be select, not set. And if select returns no rows, @TempId will not be changed. See this simple example 

declare @TempId int = 0;
select @TempId = null where 1=2;
select @TempId;

Upvotes: 2

Bhavika Zimbar
Bhavika Zimbar

Reputation: 481

At the time of selecting record, check for the NULL value, and select the record which is NOT NULL.

declare @TempId int
select top 1 @TempId = Id from Sample where Dept = @Dept and active = 1 and Id is not null

Upvotes: -1

Related Questions