1
Let’s say I have this block:
declare @teste int
declare teste_cur cursor
for
select campo1 from tabela
.....
while @@fetch_status = 0
begin
set @teste = 0
select @teste = campo1 from tabela where campo2 = condicao2
end
.....
If select returns nothing, the @test variable remains zero(0) or not?
Seeing the answers of the colleagues below, I decided by doing this IF
if @rx_num_novo is null or @rx_num_novo = ''
begin
--Aquifaço meu Update/Insert/Delete
end
2
I did a test here, and if the SELECT statement that fills in a variable does not return any row, the variable does not have its value changed.
i and..:
In your case, the fetch loop will only be executed if there is at least one entry in the outermost select. That way:
If there are records returned by the outermost select, but the condition of the inner select is not met in the last record obtained by fetch (campo2 = condicao2), the @test variable will have zero value;
If there are records returned by the most external select and the condition of the internal select is met in the last record obtained in fetch (campo2 = condicao2), the @test variable will have the field value campo1;
If the outermost select does not return records, the variable will have the default value of the integer in SQL Server, which is null.
Renan and Reginaldo, I tested the IF with null and 0 and nothing happened. In the result, the value of var appeared blank, this is the doubt, because white is not null, but I cannot assign to a var int. This is what generated the post and I have two answers now. I will test better and put what is happening. I did another post, which has to do with this too, but I had to do another, because it seems to be another question.
@pnet if you try to print the value of a null variable, you will get a blank result.
1
declare @teste int
set @teste = 0;
select @teste = campo1 from tabela where campo2 = condicao2 -- considerando que volte nulo
if ( @teste is null )
print 'nula'
else
print 'não nula'
Browser other questions tagged sql sql-server sql-server-2014
You are not signed in. Login or sign up in order to post.
No, it won’t be.
– Reginaldo Rigo
@Reginaldorigo, but how do I make an if with it then? I only get it if it’s greater than zero on Count? What’s next? Null?
– pnet
@pnet looks at my answer... You have two selects, one without condition and the other with. If there is at least one record in your table, the value of the variable will be the value of the most internal select result, otherwise it will be null.
– Oralista de Sistemas
For testing purposes: to know if SELECT returned line (and had @test checked), use @@rowcount
– José Diz
My answer select as asked in the question the variable returns null and my test works just like this in the answer. The select made by @Renan does not come back null, returns empty, but not null, which is why in the case of it the variable does not change.
– Reginaldo Rigo
@Reginaldorigo, I know, I had to mark only one. If the system allows to afford two, mark yours too.
– pnet
I wasn’t even referring to that. I hadn’t even noticed it. I was referring to the difference between the two cases.
– Reginaldo Rigo
@Reginaldorigo, it was not my intention either, just to answer. Your answer is very correct, tested and worked. Note that I tested null and empty to meet my requirement, not that I was wrong. I put Null and Empty(Empty) for convenience.
– pnet