CODEWITHSUNDEEP
sql-server
H. Pauwelyn
H. Pauwelyn

Reputation: 14280

store result of select in variable ms sql

I want to store the result of a table in a variable. I know the result is always one column and one record. Here is my code:

if ((select count(id) from Categorie where naam = 'Hobbyclub') = 1)
    begin
        declare @id int set select id from Categorie where naam = 'Hobbyclub';

        insert into Items(naam, categorieID) values ('Naaien', @id);

        print 'ok';
    end
else 
    print 'nok';

It gives following error near the keyword set:

Incorrect syntax

How can I store the first record and first column store in @id.

Note that categorieID (by the insert statement) is also a record with type int.

Upvotes: 0

Views: 36

Answers (1)

stakx - no longer contributing
stakx - no longer contributing

Reputation: 84735

Here is one way to assign a variable:

DECLARE @id INT;
SELECT @id = id FROM Categorie WHERE naam = 'Hobbyclub';

Here's another:

DECLARE @id INT;
SET @id = (SELECT id FROM …);

If you are working with SQL Server 2008 or later, the last can be abbreviated to:

DECLARE @id INT = (SELECT id FROM …);

Upvotes: 1

Related Questions