Issues with SQL Max function: "Incorrect syntax new the keyword 'SELECT' "

Question

I'm trying to write a stored procedure to return the maximum value of a column + 1 but for some reason it doesn't want to work.

DECLARE @ID int;

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

I can't for the life of me see what is wrong.

It gives me the error of:

incorrect syntax new the keyword 'SELECT'

It gives me the error of:- incorrect syntax new the keyword 'SELECT' — Tom, Mar 19 '15 at 14:59
This seems like very dangerous code. Do you know what an `identity` column is? Do you know what sequences are? There are probably better ways to accomplish what you are trying to do. — Gordon Linoff, Mar 19 '15 at 15:04
MAX(ID) + 1 is not a better way, instead that use identity column — Imran Ali Khan, Mar 19 '15 at 15:07

Giorgi Nakeuri · Answer 1 · 2015-03-19T15:12:46.133

4

No need for SET. Select value directly:

DECLARE @ID int;
SELECT @ID = MAX(ID) + 1 FROM tbl;

edited Mar 19 '15 at 15:12

answered Mar 19 '15 at 15:02

Giorgi Nakeuri

35,155
8
47
75

Hi giorgi, I am preparing for `70-461` exam,could you provide aome tipsfor passing the exam? appreciate your help! – Hare Rama Hare Krishna Apr 18 '15 at 06:40

score 3 · Accepted Answer · edited May 23 '17 at 12:28

3

Use parentheses ( ... ):

DECLARE @ID int;
SET @ID = (SELECT MAX(ID) + 1 FROM tbl);

or SELECT as suggested by Giorgi. SET is the ANSI standard way of assigning values to variables, SELECT is not. Apart from that using SELECT to assign values to variables is fine, it allows even multiple assignments with one SELECT.

But in general your query seems to be a race condition. Use an IDENTITY column if you want to autoincrement a value. Auto increment primary key in SQL Server Management Studio 2012

edited May 23 '17 at 12:28

Community

1
1

answered Mar 19 '15 at 14:59

Tim Schmelter

450,073
74
686
939

Great thanks a lot - sorry, still thumbing my way through sql – Tom Mar 19 '15 at 15:00
@Tom: not sure what you're doing there, but maybe you should use an `Identity` column instead: http://stackoverflow.com/questions/10991894/auto-increment-primary-key-in-sql-server-management-studio-2012 Then this works automatically and you have no race condition. – Tim Schmelter Mar 19 '15 at 15:02
I would love to use an identity column, but this table stores lots of versions of the same ID - bad planning when I set it up. – Tom Mar 19 '15 at 15:03
1

@Tom then you need to fix it. Slapping more code at a known problem is not a good approach. This is going to cause you nothing but pain in the long run. Setup some sequences, create a table for the identity for each type of version or whatever. MAX(ID) + 1 will end up with duplicates and cause nothing but grief....I speak from experience here. – Sean Lange Mar 19 '15 at 15:26
I appreciate your points and realise this isn't a good approach but I haven't got the time at the moment to redo the front end of it. I will amend the table in v2.0 – Tom Mar 19 '15 at 15:57

score 2 · Answer 3 · answered Mar 19 '15 at 15:22

2

You need to consider a scenario when there is no value in the table and MAX returns NULL.

DECLARE @ID int;

SELECT @ID = ISNULL(MAX(ID) , 0) + 1 FROM tbl;

Other adding 1 to null will always yield null.

answered Mar 19 '15 at 15:22

M.Ali

67,945
13
101
127

score 1 · Answer 4 · answered Mar 19 '15 at 15:03

1

DECLARE @ID int;
SET @ID = (SELECT MAX(ID) + 1 FROM tbl);

parentheses operator ()

for more information

https://msdn.microsoft.com/en-us/library/ms190276.aspx

answered Mar 19 '15 at 15:03

Imran Ali Khan

8,469
16
52
77

Issues with SQL Max function: "Incorrect syntax new the keyword 'SELECT' "

4 Answers4