Set a single returned result from sql query as a variable

Question

I have a query that is returning a single result. I would like to set that as a variable value in the query to be used later on. It shows up often with all the sub querys that I have. So I would like to

DECLARE @NEWID AS INT = 55

IdNumber
55

Answer 1

There's 2 ways to get a value from a table into a variable.

1. Set

DECLARE @NEWID AS INT;

SET @NEWID = (SELECT MAX(ID)+1 FROM SomeTable);

But the query may only return a single value.

2. Select

DECLARE @NEWID AS INT;

SELECT @NEWID = MAX(ID)+1 
FROM SomeTable;

The variable will become the last value that would be returned by the query.