Use select top to declare a variable

Asked

Viewed 293 times

2

I wanted to assign the value of the last item registered in the table alu_cod, but the query returns me error after the @total =:

declare @total int
select @total = SELECT TOP 1 * FROM Alunos ORDER BY 
Alu_COD DESC  
select @total
c# sql-server

1 answer

2

Your syntax is incorrect. You can assign the value of a SELECT to a variable as follows:

Using SELECT:

SELECT TOP 1 @total = Alu_COD 
  FROM Alunos 
 ORDER BY Alu_COD DESC  
SELECT @total

Set

SET @total= (SELECT TOP 1 Alu_COD 
               FROM Alunos 
              ORDER BY Alu_COD DESC )
SELECT @total

Heed by using the first alternative. If your query returns more than one record (this is not the case here, because it is using TOP), this alternative will assign the variable the last record that is returned by the query, without showing any error message (this may cause bugs in your application/script). The second alternative, using SET, under the same circumstances will show an error message.

Above are examples on how to assign the value of a single column to a variable, if you want to select the complete record, you have to define a variable for each column in SELECT or, preferably, define a TABLE VARIABLE.

Using a variable table

DECLARE @alunos TABLE (
  Alu_COD  int,
  Alu_NOME VARCHAR(50),
  Alu_DATA_REGISTO DATE
)
INSERT INTO @alunos (Alu_COD, Alu_NOME, Alu_DATA)
SELECT TOP 1 Alu_COD, Alu_NOME, Alu_DATA
  FROM Alunos 
 ORDER BY Alu_COD DESC

Defining the variables individually

DECLARE @Alu_COD  int,
        @Alu_NOME VARCHAR(50),
        @Alu_DATA_REGISTO DATE

SELECT TOP 1 @Alu_COD = Alu_COD,
       @Alu_NOME = Alu_NOME,
       @Alu_DATA_REGISTO = Alu_DATA_REGISTO
  FROM Alunos
 ORDER BY Alu_COD DESC

Browser other questions tagged c# sql-server

You are not signed in. Login or sign up in order to post.