SQL Server script syntax errors and “must declare the scalar value”

2
votes

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.

2
sql-server

2 Answers

3
votes

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

5
votes

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.