Syntax error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Syntax error

Author

Topic

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2012-02-03 : 12:19:02

Greetings experts,

When I run the following query:

INSERT INTO EMP (UserSequence, employee_id, charity_code, check_amt, chcknum, one_time, bi_weekly, cash, donate_choice, date_stamp) VALUES ((select isNull(max(UserSequence), 0) + 1, '0000025700','AC418','333','','0','0','','Yes','2/3/2012 12:15:20 PM');

I get following error:

Subqueries are not allowed in this context. Only scalar expressions are allowed.

It has to do with

((select isNull(max(UserSequence), 0) +

Any ideas how to resolve this?

Thanks alot in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-03 : 12:29:13

[code]
INSERT INTO EMP (UserSequence, employee_id, charity_code, check_amt, chcknum, one_time, bi_weekly, cash, donate_choice, date_stamp)
select isNull(max(UserSequence), 0) + 1, '0000025700','AC418','333','','0','0','','Yes','2/3/2012 12:15:20 PM'
FROM EMP
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2012-02-03 : 13:26:20

Thank you very much visakh16,

Your code won't work for me because the users are actually entering the data, we are not selecting them from the same table we are saving them to.

All we are trying to do is to increment the usersequence id after an insert.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-03 : 13:32:32

then put it in variable and use


DECLARE @SeqID int

select @SeqID = isNull(max(UserSequence), 0) + 1 FROM EMP

INSERT INTO EMP (UserSequence, employee_id, charity_code, check_amt, chcknum, one_time, bi_weekly, cash, donate_choice, date_stamp) 
VALUES(@SeqID, '0000025700','AC418','333','','0','0','','Yes','2/3/2012 12:15:20 PM')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2012-02-03 : 14:01:25

Thanks again very much.

I guess I am stuck with that process.

That's exactly what I was using until I was told by management that using it that way would create concurrency issue.

I may have to settle for that and deal with the consequences.

Unless you have other magic up your sleeves.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-03 : 14:14:05

even otherwise concurrency issue will exist
thats the problem with taking mx id approach
you could have kept it simple by making it an auto incrementing id column and then doing insert inside a transaction

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources