syntax error in SELECT statement

Question

With my limited SQL knowledge, I'm unable to figure this out. What's wrong with the following:

DECLARE @Id int
DECLARE @Name varchar(40)

WHILE EXISTS
        (
        SELECT TOP 1 @Name = Name, @Id=ID FROM MyTable c WHERE <CONDITION>
        )
BEGIN
    SOME MORE SQL using @Id and @Name
END

I get a syntax error near @Name = Name

EDIT

To add more context to the problem, I have two tables named Category (ID, Name, ParentID) and Account(ID, Name, CategoryID).

There are 3 levels of Categories.

Root
Category
SubCategory

This is achieved using a recursive relation (ParentID > CategoryID). Problem to solve is that if there are any Accounts that belongs to a Category X (level 2), we must

Create a SubCategory Y (level 3) with the same name as X
Make Y a child of that X
Move all Accounts from X to Y

Here is the original script I have written:

DECLARE @Id int
DECLARE @Name varchar(40)

WHILE EXISTS(
        SELECT TOP 1 @Name=Name, @Id=CategoryID FROM Category c WHERE 
        ParentID = (SELECT TOP 1 CategoryID FROM Category WHERE Name = 'Root') AND
        (SELECT COUNT(*) FROM Account WHERE CategoryID = c.CategoryID) > 0
        )
BEGIN
    INSERT INTO Category(Name, ParentID) VALUES(@Name, @Id)
    UPDATE Account SET CategoryID = @@IDENTITY WHERE CategoryID = @Id  
END

SELECT TOP 1 is expecting the next thing to be a column name, but you're providing an assignment expression. (Id risk suggesting, even though i might be very wrong) — Felype
– Felype, Commented May 19, 2016 at 12:40
What is there in SOME MORE SQL ...? This smells like procedural approach. Might be an XY-Problem. I'm quite sure, that there'll be a set-based approach too... — Gottfried Lesigang
– Gottfried Lesigang, Commented May 19, 2016 at 12:44

Lukasz Szozda · Accepted Answer · 2016-05-19 12:45:09Z

2

You could use CURSOR loop:

CREATE TABLE MyTable(ID INT, Name VARCHAR(100));
INSERT INTO Mytable(ID, Name) VALUES (1,10),(2,20);

DECLARE @Id int;
DECLARE @Name varchar(40);

DECLARE cur CURSOR FAST_FORWARD FOR SELECT Name,ID FROM MyTable c WHERE 1=1;

OPEN cur;
FETCH NEXT FROM cur INTO @Id, @Name;

WHILE @@FETCH_STATUS = 0
BEGIN
   SELECT @Id,@Name;   

   FETCH NEXT FROM cur INTO @Id, @Name;
END;

CLOSE cur;
DEALLOCATE cur;

LiveDemo

Please note that TOP 1 without explicit ORDER BY may produce different results between executions.

EXISTS:

EXISTS subquery

Note that SELECT @ID = id, @Name = Name is not subquery but assignment.

CREATE TABLE MyTable(ID INT, Name VARCHAR(100));
INSERT INTO Mytable(Id, Name) VALUES (1,10),(2,20);

DECLARE @Id int;
DECLARE @Name varchar(40);

SELECT 1
WHERE EXISTS (SELECT @id = id FROM Mytable);  -- assignment
-- Incorrect syntax near '='.

SELECT 1
WHERE EXISTS (SELECT id FROM Mytable);        -- subquery

LiveDemo 2

edited May 19, 2016 at 12:45

answered May 19, 2016 at 12:40

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

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

3 Comments

Stuart Ainsworth Over a year ago

This is a good solution to the immediate problem; the larger question is why does the OP need to use a loop to begin with. There are always situations, but they should be rare.

dotNET Over a year ago

Thanks for the effort and providing an actual solution. @StuartAinsworth: I have added more detail to the question. Maybe that helps u to suggest an even better solution?

dotNET Over a year ago

Thanks a lot. Your cursor approach did it for me.

Krishnraj Rana · Accepted Answer · 2016-05-19 13:07:29Z

1

There is no need to use While, you can use if condition instead of while like this - `

DECLARE @Id int
DECLARE @Name varchar(40)

SELECT TOP 1 @Name = Name, @Id=ID FROM MyTable c WHERE <CONDITION>

-- Now Check for @Name and @ID
IF ISNULL(@Name, '') <> '' AND ISNULL(@Id, 0) <> 0
BEGIN
      SOME MORE SQL using @Id and @Name
END

EDITED

IF EXISTS(SELECT 1 FROM Category c 
            WHERE ParentID = (SELECT TOP 1 CategoryID FROM Category WHERE Name = 'Root')  
            AND (SELECT COUNT(*) FROM Account WHERE CategoryID = c.CategoryID) > 0
        )
BEGIN

     SELECT TOP 1 @Name=Name, @Id=CategoryID FROM Category c 
        WHERE ParentID = (SELECT TOP 1 CategoryID FROM Category WHERE Name = 'Root') 
        AND (SELECT COUNT(*) FROM Account WHERE CategoryID = c.CategoryID) > 0

    INSERT INTO Category(Name, ParentID) 
      VALUES(@Name, @Id)

    SET @CategoryID = SCOPE_IDENTITY()

    UPDATE Account SET CategoryID = @CategoryID WHERE CategoryID = @Id  

END

edited May 19, 2016 at 13:07

answered May 19, 2016 at 12:44

Krishnraj Rana

6,6583 gold badges33 silver badges37 bronze badges

2 Comments

Krishnraj Rana Over a year ago

@dotNET: See my Edited answer. Hope it help you.

dotNET Over a year ago

How will this script fix it for ALL categories?

valex · Accepted Answer · 2016-05-19 13:16:51Z

1

SELECT @local_variable (Transact-SQL)

A SELECT statement that contains a variable assignment cannot be used to also perform typical result set retrieval operations.

Here you use EXISTS to retrieve data so I guess you can't use assignment inside a subquery.

Try to implement equivalent WHILE TRUE loop with BREAK.

DECLARE @Id int
DECLARE @Name varchar(40)

WHILE (1=1)

BEGIN

SET @Name = NULL;
SET @id = NULL;

SELECT TOP 1 @Name=Name, @Id=CategoryID FROM Category c WHERE 
        ParentID = (SELECT TOP 1 CategoryID FROM Category WHERE Name = 'Root') 
         AND
        (SELECT COUNT(*) FROM Account WHERE CategoryID = c.CategoryID) > 0
        );

    IF (@ID IS NULL) BREAK;

    INSERT INTO Category(Name, ParentID) VALUES(@Name, @Id);
    UPDATE Account SET CategoryID = @@IDENTITY WHERE CategoryID = @Id ; 
END

edited May 19, 2016 at 13:16

answered May 19, 2016 at 12:55

valex

24.2k7 gold badges47 silver badges62 bronze badges

3 Comments

dotNET Over a year ago

Very precise. Actually answers the question. What would be the correct line of action for me (see my edit)?

valex Over a year ago

@dotNET I've added an equivalent WHILE TRUE loop. Please test it.

dotNET Over a year ago

Nicely done. I like lad2025's answer too. Can't select more than one answer, but this one definitely deserves an upvote.

BoogaWooga · Accepted Answer · 2016-05-19 12:49:10Z

0

Use this for check record existence

WHILE EXISTS
    (
    SELECT TOP 1 * FROM MyTable c WHERE <CONDITION>
    )
BEGIN
    SELECT TOP 1 @Name = Name, @Id=ID FROM MyTable c WHERE <CONDITION>

-- and do here what you want
END

edited May 19, 2016 at 12:49

BoogaWooga

1087 bronze badges

answered May 19, 2016 at 12:46

Santosh Pandey

1113 bronze badges

Collectives™ on Stack Overflow

syntax error in SELECT statement

EDIT

4 Answers 4

3 Comments

2 Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

EDIT

4 Answers 4

3 Comments

2 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related