SQL Server select variable where no results

Can I set variables if the query returns no results?

DECLARE @Output AS VARCHAR(MAX);



SELECT @Output = 'Old';

SELECT @Output = 'New' WHERE 0 = 1;



PRINT @Output;

Output value is: Old

Expected value is: NULL

edited Nov 19 '18 at 21:38

marc_s

572k12811061253

asked Nov 19 '18 at 16:54

profimedica

1,4001630

2

Why do you expect it to be null? The 0=1 is false, so it's not going to set the variable at all.

– dcp
Nov 19 '18 at 16:56

1

A query that returns no rows does not return NULL, it returns no rows. If you aren't passing a value (including NULL) to the assignment, then the variable won't be updated, no value was passed to assign it. NULL is not the absence of a value, it is an unknown value; which is still a value.

– Larnu
Nov 19 '18 at 17:00

You could check the value of @@RowCount after the second select to determine if a row was processed. It will be zero in this case.

– HABO
Nov 19 '18 at 17:34

2

You are now experiencing the difference between using SELECT and SET to assign a variable. Had you used SET, the variable would be NULL after the SET statement executes. SELECT has the side effect of NOT changing the variable if no rows are selected.

– SMor
Nov 19 '18 at 18:36

add a comment |

Can I set variables if the query returns no results?

DECLARE @Output AS VARCHAR(MAX);



SELECT @Output = 'Old';

SELECT @Output = 'New' WHERE 0 = 1;



PRINT @Output;

Output value is: Old

Expected value is: NULL

edited Nov 19 '18 at 21:38

marc_s

572k12811061253

asked Nov 19 '18 at 16:54

profimedica

1,4001630

2

Why do you expect it to be null? The 0=1 is false, so it's not going to set the variable at all.

– dcp
Nov 19 '18 at 16:56

1

A query that returns no rows does not return NULL, it returns no rows. If you aren't passing a value (including NULL) to the assignment, then the variable won't be updated, no value was passed to assign it. NULL is not the absence of a value, it is an unknown value; which is still a value.

– Larnu
Nov 19 '18 at 17:00

You could check the value of @@RowCount after the second select to determine if a row was processed. It will be zero in this case.

– HABO
Nov 19 '18 at 17:34

2

You are now experiencing the difference between using SELECT and SET to assign a variable. Had you used SET, the variable would be NULL after the SET statement executes. SELECT has the side effect of NOT changing the variable if no rows are selected.

– SMor
Nov 19 '18 at 18:36

add a comment |

Can I set variables if the query returns no results?

DECLARE @Output AS VARCHAR(MAX);



SELECT @Output = 'Old';

SELECT @Output = 'New' WHERE 0 = 1;



PRINT @Output;

Output value is: Old

Expected value is: NULL

edited Nov 19 '18 at 21:38

marc_s

572k12811061253

asked Nov 19 '18 at 16:54

profimedica

1,4001630

Can I set variables if the query returns no results?

DECLARE @Output AS VARCHAR(MAX);



SELECT @Output = 'Old';

SELECT @Output = 'New' WHERE 0 = 1;



PRINT @Output;

Output value is: Old

Expected value is: NULL

sql sql-server tsql

edited Nov 19 '18 at 21:38

marc_s

572k12811061253

asked Nov 19 '18 at 16:54

profimedica

1,4001630

edited Nov 19 '18 at 21:38

marc_s

572k12811061253

asked Nov 19 '18 at 16:54

profimedica

1,4001630

edited Nov 19 '18 at 21:38

marc_s

572k12811061253

edited Nov 19 '18 at 21:38

marc_s

572k12811061253

edited Nov 19 '18 at 21:38

marc_s

572k12811061253

asked Nov 19 '18 at 16:54

profimedica

1,4001630

asked Nov 19 '18 at 16:54

profimedica

1,4001630

asked Nov 19 '18 at 16:54

profimedica

1,4001630

2

Why do you expect it to be null? The 0=1 is false, so it's not going to set the variable at all.

– dcp
Nov 19 '18 at 16:56

1

A query that returns no rows does not return NULL, it returns no rows. If you aren't passing a value (including NULL) to the assignment, then the variable won't be updated, no value was passed to assign it. NULL is not the absence of a value, it is an unknown value; which is still a value.

– Larnu
Nov 19 '18 at 17:00

You could check the value of @@RowCount after the second select to determine if a row was processed. It will be zero in this case.

– HABO
Nov 19 '18 at 17:34

2

You are now experiencing the difference between using SELECT and SET to assign a variable. Had you used SET, the variable would be NULL after the SET statement executes. SELECT has the side effect of NOT changing the variable if no rows are selected.

– SMor
Nov 19 '18 at 18:36

add a comment |

2

Why do you expect it to be null? The 0=1 is false, so it's not going to set the variable at all.

– dcp
Nov 19 '18 at 16:56

1

A query that returns no rows does not return NULL, it returns no rows. If you aren't passing a value (including NULL) to the assignment, then the variable won't be updated, no value was passed to assign it. NULL is not the absence of a value, it is an unknown value; which is still a value.

– Larnu
Nov 19 '18 at 17:00

You could check the value of @@RowCount after the second select to determine if a row was processed. It will be zero in this case.

– HABO
Nov 19 '18 at 17:34

2

You are now experiencing the difference between using SELECT and SET to assign a variable. Had you used SET, the variable would be NULL after the SET statement executes. SELECT has the side effect of NOT changing the variable if no rows are selected.

– SMor
Nov 19 '18 at 18:36

Why do you expect it to be null? The 0=1 is false, so it's not going to set the variable at all.

– dcp
Nov 19 '18 at 16:56

A query that returns no rows does not return NULL, it returns no rows. If you aren't passing a value (including NULL) to the assignment, then the variable won't be updated, no value was passed to assign it. NULL is not the absence of a value, it is an unknown value; which is still a value.

– Larnu
Nov 19 '18 at 17:00

You could check the value of @@RowCount after the second select to determine if a row was processed. It will be zero in this case.

– HABO
Nov 19 '18 at 17:34

You are now experiencing the difference between using SELECT and SET to assign a variable. Had you used SET, the variable would be NULL after the SET statement executes. SELECT has the side effect of NOT changing the variable if no rows are selected.

– SMor
Nov 19 '18 at 18:36

add a comment |

4 Answers
4

active

oldest

votes

DECLARE @Output AS VARCHAR(MAX);

SELECT @Output = 'Old';

SELECT @Output = 'New' WHERE 0 = 1;

PRINT @Output;

You get 'Old' cause the variable already has this value, and it will not update the value since in the WHERE clause you use the condition 0=1, which will return FALSE and the value in the variable won't change.

WHERE 0 = 1 It will be False

WHERE 0 <> 1 It will be True

It's just similar to IF 0=1 THEN UpdateMyVar

So in your case the value will always 'Old', it won't return 'New' or NULL either.

I don't know what are you trying to do really, but if you want to return NULL then

DECLARE @Output AS VARCHAR(MAX);

SELECT @Output = 'Old';

SELECT @Output = NULL WHERE 0 <> 1; --If 0<> 1 then update my var and set NULL else leave it as it is

PRINT @Output;

edited Nov 19 '18 at 17:07

answered Nov 19 '18 at 16:57

Sami

8,17731240

My Where condition is set to false because I need to simulate a query that returns no result. There are side other effects in my context if I change my where condition. So, I need to alter the variable on an empty resultset.

– profimedica
Nov 19 '18 at 17:25

1

@profimedica - you can't in the SELECT if the resultset is empty. You can check @@ROWCOUNT immediately after and do something there.

– Martin Smith
Nov 19 '18 at 17:33

add a comment |

Try this:

DECLARE @Output AS VARCHAR(MAX);

SELECT @Output = 'Old';

SELECT @Output = CASE 0 WHEN 1 THEN 'New' ELSE NULL END

SELECT @Output;

answered Nov 19 '18 at 17:00

Barry Kaye

6,96353055

1

This doesn't address the op.

– Yogesh Sharma
Nov 19 '18 at 17:01

Your switch case works always on false branch. It is a valid syntax but my code was isolated from a complex procedure where the WHERE condition has multiple conditions. For readability I simplified it in this question.

– profimedica
Nov 19 '18 at 17:29

add a comment |

Sami provides a good explanation of what is going on.

If you want to be sure that a value is assigned, then you need to be sure that the query returns one row for the assignment. One way to do this is to use aggregations:

DECLARE @Output AS VARCHAR(MAX);

SELECT @Output = 'Old';

SELECT @Output = MAX('New') WHERE 0 = 1;

SELECT @Output;

answered Nov 19 '18 at 18:44

Gordon Linoff

762k35296400

add a comment |

This is simply how assigning variables in SELECT is implemented in SQL Server. As @SMor said in the comments, use SET instead of SELECT.

DECLARE @Output AS VARCHAR(MAX);



SET @Output = 'Old';

SET @Output = (SELECT 'New' WHERE 0 = 1);

-- this would change the @Output value to NULL



SELECT @Output;

The only problem with SET is that it can assign only one variable, while in SELECT you can assign many variables at the same time.

answered Nov 20 '18 at 0:01

Vladimir Baranov

22k32860

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53379314%2fsql-server-select-variable-where-no-results%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

4 Answers
4

active

oldest

votes

4 Answers
4

active

oldest

votes

DECLARE @Output AS VARCHAR(MAX);

SELECT @Output = 'Old';

SELECT @Output = 'New' WHERE 0 = 1;

PRINT @Output;

WHERE 0 = 1 It will be False

WHERE 0 <> 1 It will be True

It's just similar to IF 0=1 THEN UpdateMyVar

So in your case the value will always 'Old', it won't return 'New' or NULL either.

I don't know what are you trying to do really, but if you want to return NULL then

DECLARE @Output AS VARCHAR(MAX);

SELECT @Output = 'Old';

SELECT @Output = NULL WHERE 0 <> 1; --If 0<> 1 then update my var and set NULL else leave it as it is

PRINT @Output;

edited Nov 19 '18 at 17:07

answered Nov 19 '18 at 16:57

Sami

8,17731240

My Where condition is set to false because I need to simulate a query that returns no result. There are side other effects in my context if I change my where condition. So, I need to alter the variable on an empty resultset.

– profimedica
Nov 19 '18 at 17:25

1

@profimedica - you can't in the SELECT if the resultset is empty. You can check @@ROWCOUNT immediately after and do something there.

– Martin Smith
Nov 19 '18 at 17:33

add a comment |

DECLARE @Output AS VARCHAR(MAX);

SELECT @Output = 'Old';

SELECT @Output = 'New' WHERE 0 = 1;

PRINT @Output;

WHERE 0 = 1 It will be False

WHERE 0 <> 1 It will be True

It's just similar to IF 0=1 THEN UpdateMyVar

So in your case the value will always 'Old', it won't return 'New' or NULL either.

I don't know what are you trying to do really, but if you want to return NULL then

DECLARE @Output AS VARCHAR(MAX);

SELECT @Output = 'Old';

SELECT @Output = NULL WHERE 0 <> 1; --If 0<> 1 then update my var and set NULL else leave it as it is

PRINT @Output;

edited Nov 19 '18 at 17:07

answered Nov 19 '18 at 16:57

Sami

8,17731240

My Where condition is set to false because I need to simulate a query that returns no result. There are side other effects in my context if I change my where condition. So, I need to alter the variable on an empty resultset.

– profimedica
Nov 19 '18 at 17:25

1

@profimedica - you can't in the SELECT if the resultset is empty. You can check @@ROWCOUNT immediately after and do something there.

– Martin Smith
Nov 19 '18 at 17:33

add a comment |

DECLARE @Output AS VARCHAR(MAX);

SELECT @Output = 'Old';

SELECT @Output = 'New' WHERE 0 = 1;

PRINT @Output;

WHERE 0 = 1 It will be False

WHERE 0 <> 1 It will be True

It's just similar to IF 0=1 THEN UpdateMyVar

So in your case the value will always 'Old', it won't return 'New' or NULL either.

I don't know what are you trying to do really, but if you want to return NULL then

DECLARE @Output AS VARCHAR(MAX);

SELECT @Output = 'Old';

SELECT @Output = NULL WHERE 0 <> 1; --If 0<> 1 then update my var and set NULL else leave it as it is

PRINT @Output;

edited Nov 19 '18 at 17:07

answered Nov 19 '18 at 16:57

Sami

8,17731240

DECLARE @Output AS VARCHAR(MAX);

SELECT @Output = 'Old';

SELECT @Output = 'New' WHERE 0 = 1;

PRINT @Output;

WHERE 0 = 1 It will be False

WHERE 0 <> 1 It will be True

It's just similar to IF 0=1 THEN UpdateMyVar

So in your case the value will always 'Old', it won't return 'New' or NULL either.

I don't know what are you trying to do really, but if you want to return NULL then

DECLARE @Output AS VARCHAR(MAX);

SELECT @Output = 'Old';

SELECT @Output = NULL WHERE 0 <> 1; --If 0<> 1 then update my var and set NULL else leave it as it is

PRINT @Output;

edited Nov 19 '18 at 17:07

answered Nov 19 '18 at 16:57

Sami

8,17731240

edited Nov 19 '18 at 17:07

answered Nov 19 '18 at 16:57

Sami

8,17731240

answered Nov 19 '18 at 16:57

Sami

8,17731240

answered Nov 19 '18 at 16:57

Sami

8,17731240

My Where condition is set to false because I need to simulate a query that returns no result. There are side other effects in my context if I change my where condition. So, I need to alter the variable on an empty resultset.

– profimedica
Nov 19 '18 at 17:25

1

@profimedica - you can't in the SELECT if the resultset is empty. You can check @@ROWCOUNT immediately after and do something there.

– Martin Smith
Nov 19 '18 at 17:33

add a comment |

My Where condition is set to false because I need to simulate a query that returns no result. There are side other effects in my context if I change my where condition. So, I need to alter the variable on an empty resultset.

– profimedica
Nov 19 '18 at 17:25

1

@profimedica - you can't in the SELECT if the resultset is empty. You can check @@ROWCOUNT immediately after and do something there.

– Martin Smith
Nov 19 '18 at 17:33

My Where condition is set to false because I need to simulate a query that returns no result. There are side other effects in my context if I change my where condition. So, I need to alter the variable on an empty resultset.

– profimedica
Nov 19 '18 at 17:25

@profimedica - you can't in the SELECT if the resultset is empty. You can check @@ROWCOUNT immediately after and do something there.

– Martin Smith
Nov 19 '18 at 17:33

add a comment |

Try this:

DECLARE @Output AS VARCHAR(MAX);

SELECT @Output = 'Old';

SELECT @Output = CASE 0 WHEN 1 THEN 'New' ELSE NULL END

SELECT @Output;

answered Nov 19 '18 at 17:00

Barry Kaye

6,96353055

1

This doesn't address the op.

– Yogesh Sharma
Nov 19 '18 at 17:01

Your switch case works always on false branch. It is a valid syntax but my code was isolated from a complex procedure where the WHERE condition has multiple conditions. For readability I simplified it in this question.

– profimedica
Nov 19 '18 at 17:29

add a comment |

Try this:

DECLARE @Output AS VARCHAR(MAX);

SELECT @Output = 'Old';

SELECT @Output = CASE 0 WHEN 1 THEN 'New' ELSE NULL END

SELECT @Output;

answered Nov 19 '18 at 17:00

Barry Kaye

6,96353055

1

This doesn't address the op.

– Yogesh Sharma
Nov 19 '18 at 17:01

Your switch case works always on false branch. It is a valid syntax but my code was isolated from a complex procedure where the WHERE condition has multiple conditions. For readability I simplified it in this question.

– profimedica
Nov 19 '18 at 17:29

add a comment |

Try this:

DECLARE @Output AS VARCHAR(MAX);

SELECT @Output = 'Old';

SELECT @Output = CASE 0 WHEN 1 THEN 'New' ELSE NULL END

SELECT @Output;

answered Nov 19 '18 at 17:00

Barry Kaye

6,96353055

Try this:

DECLARE @Output AS VARCHAR(MAX);

SELECT @Output = 'Old';

SELECT @Output = CASE 0 WHEN 1 THEN 'New' ELSE NULL END

SELECT @Output;

answered Nov 19 '18 at 17:00

Barry Kaye

6,96353055

answered Nov 19 '18 at 17:00

Barry Kaye

6,96353055

answered Nov 19 '18 at 17:00

Barry Kaye

6,96353055

answered Nov 19 '18 at 17:00

Barry Kaye

6,96353055

1

This doesn't address the op.

– Yogesh Sharma
Nov 19 '18 at 17:01

Your switch case works always on false branch. It is a valid syntax but my code was isolated from a complex procedure where the WHERE condition has multiple conditions. For readability I simplified it in this question.

– profimedica
Nov 19 '18 at 17:29

add a comment |

1

This doesn't address the op.

– Yogesh Sharma
Nov 19 '18 at 17:01

Your switch case works always on false branch. It is a valid syntax but my code was isolated from a complex procedure where the WHERE condition has multiple conditions. For readability I simplified it in this question.

– profimedica
Nov 19 '18 at 17:29

This doesn't address the op.

– Yogesh Sharma
Nov 19 '18 at 17:01

Your switch case works always on false branch. It is a valid syntax but my code was isolated from a complex procedure where the WHERE condition has multiple conditions. For readability I simplified it in this question.

– profimedica
Nov 19 '18 at 17:29

add a comment |

Sami provides a good explanation of what is going on.

If you want to be sure that a value is assigned, then you need to be sure that the query returns one row for the assignment. One way to do this is to use aggregations:

DECLARE @Output AS VARCHAR(MAX);

SELECT @Output = 'Old';

SELECT @Output = MAX('New') WHERE 0 = 1;

SELECT @Output;

answered Nov 19 '18 at 18:44

Gordon Linoff

762k35296400

add a comment |

Sami provides a good explanation of what is going on.

If you want to be sure that a value is assigned, then you need to be sure that the query returns one row for the assignment. One way to do this is to use aggregations:

DECLARE @Output AS VARCHAR(MAX);

SELECT @Output = 'Old';

SELECT @Output = MAX('New') WHERE 0 = 1;

SELECT @Output;

answered Nov 19 '18 at 18:44

Gordon Linoff

762k35296400

add a comment |

Sami provides a good explanation of what is going on.

If you want to be sure that a value is assigned, then you need to be sure that the query returns one row for the assignment. One way to do this is to use aggregations:

DECLARE @Output AS VARCHAR(MAX);

SELECT @Output = 'Old';

SELECT @Output = MAX('New') WHERE 0 = 1;

SELECT @Output;

answered Nov 19 '18 at 18:44

Gordon Linoff

762k35296400

Sami provides a good explanation of what is going on.

If you want to be sure that a value is assigned, then you need to be sure that the query returns one row for the assignment. One way to do this is to use aggregations:

DECLARE @Output AS VARCHAR(MAX);

SELECT @Output = 'Old';

SELECT @Output = MAX('New') WHERE 0 = 1;

SELECT @Output;

answered Nov 19 '18 at 18:44

Gordon Linoff

762k35296400

answered Nov 19 '18 at 18:44

Gordon Linoff

762k35296400

answered Nov 19 '18 at 18:44

Gordon Linoff

762k35296400

answered Nov 19 '18 at 18:44

Gordon Linoff

762k35296400

add a comment |

This is simply how assigning variables in SELECT is implemented in SQL Server. As @SMor said in the comments, use SET instead of SELECT.

DECLARE @Output AS VARCHAR(MAX);



SET @Output = 'Old';

SET @Output = (SELECT 'New' WHERE 0 = 1);

-- this would change the @Output value to NULL



SELECT @Output;

The only problem with SET is that it can assign only one variable, while in SELECT you can assign many variables at the same time.

answered Nov 20 '18 at 0:01

Vladimir Baranov

22k32860

add a comment |

This is simply how assigning variables in SELECT is implemented in SQL Server. As @SMor said in the comments, use SET instead of SELECT.

DECLARE @Output AS VARCHAR(MAX);



SET @Output = 'Old';

SET @Output = (SELECT 'New' WHERE 0 = 1);

-- this would change the @Output value to NULL



SELECT @Output;

The only problem with SET is that it can assign only one variable, while in SELECT you can assign many variables at the same time.

answered Nov 20 '18 at 0:01

Vladimir Baranov

22k32860

add a comment |

This is simply how assigning variables in SELECT is implemented in SQL Server. As @SMor said in the comments, use SET instead of SELECT.

DECLARE @Output AS VARCHAR(MAX);



SET @Output = 'Old';

SET @Output = (SELECT 'New' WHERE 0 = 1);

-- this would change the @Output value to NULL



SELECT @Output;

The only problem with SET is that it can assign only one variable, while in SELECT you can assign many variables at the same time.

answered Nov 20 '18 at 0:01

Vladimir Baranov

22k32860

This is simply how assigning variables in SELECT is implemented in SQL Server. As @SMor said in the comments, use SET instead of SELECT.

DECLARE @Output AS VARCHAR(MAX);



SET @Output = 'Old';

SET @Output = (SELECT 'New' WHERE 0 = 1);

-- this would change the @Output value to NULL



SELECT @Output;

The only problem with SET is that it can assign only one variable, while in SELECT you can assign many variables at the same time.

answered Nov 20 '18 at 0:01

Vladimir Baranov

22k32860

answered Nov 20 '18 at 0:01

Vladimir Baranov

22k32860

answered Nov 20 '18 at 0:01

Vladimir Baranov

22k32860

answered Nov 20 '18 at 0:01

Vladimir Baranov

22k32860

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

Search This Blog

Ufyukyu