SQL Server script syntax errors and "must declare the scalar value"

Question

I'm trying to write a script in T-SQL to resolve a critical issue on our system. Unfortunately I have never done this before so I'm entering uncharted waters.

I'm actually writing a PHP script to generate this SQL. If the user exists, create it. But in both cases, add the user to a group.

DECLARE @userId AS int

/* --------------- 123l123123fvsdf - Name --------------- */
    BEGIN
        IF EXISTS(SELECT id FROM [CMT_UAT].[dbo].[user] WHERE username = '123l123123fvsdf')
                INSERT INTO [CMT_UAT].[dbo].[user](username, password, email, firstname, lastname, created) VALUES('123l123123fvsdf', 'PASSWORD', '[email protected]', 'firstName', 'lastName', GETDATE())
                GO
                SET @userId = @@IDENTITY
        ELSE
                SET @userId = SELECT id FROM [CMT_UAT].[dbo].[user] WHERE username = 'AA00043'
    END
    INSERT INTO [CMT_UAT].[dbo].[user_group](user_id, group_id) VALUES(@userId, '7')

I'm getting these errors:

Msg 102, Level 15, State 1, Line 7
Incorrect syntax near ')'.
Msg 137, Level 15, State 1, Line 1
Must declare the scalar variable "@userId".
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'SELECT'.
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'INSERT'.
Msg 137, Level 15, State 2, Line 5
Must declare the scalar variable "@userId".

I've done some googling on the scalar value and it can happen when my query returns multiple rows, which I've verified that it doesn't.

RBarryYoung · Accepted Answer · 2012-12-05 19:33:48Z

5

Take the "GO" out of your script. "GO" is a provider instruction that tells the connect manager to end the execution batch and to start a new one. Thus, it's resetting your execution context , wiping out your @userid variable declaration.

answered Dec 5, 2012 at 19:33

RBarryYoung

57k15 gold badges99 silver badges142 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

DiverseAndRemote.com · Accepted Answer · 2012-12-05 19:32:10Z

3

you need to fix your if statement. try:

DECLARE @userId AS int

IF EXISTS(SELECT id FROM [CMT_UAT].[dbo].[user] WHERE username = '123l123123fvsdf')
BEGIN
    INSERT INTO [CMT_UAT].[dbo].[user](username, password, email, firstname, lastname, created) VALUES('123l123123fvsdf', 'PASSWORD', '[email protected]', 'firstName', 'lastName', GETDATE())
    SET @userId = @@IDENTITY
END
ELSE
   SET @userId = SELECT id FROM [CMT_UAT].[dbo].[user] WHERE username = 'AA00043'

INSERT INTO [CMT_UAT].[dbo].[user_group](user_id, group_id) VALUES(@userId, '7')

you have multipl statements in the first part of your if block but you forgot to put BEGIN and END around it

And also like RBarryYoung pointed out you need to take the GO out

answered Dec 5, 2012 at 19:32

DiverseAndRemote.com

20k10 gold badges65 silver badges75 bronze badges

2 Comments

Ben Over a year ago

Good call, that helped. It still doesn't like the syntax near SELECT

Ben Over a year ago

Got it, needed to be within parenthesis

Collectives™ on Stack Overflow

SQL Server script syntax errors and "must declare the scalar value"

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related