Set variable with SELECT and WITH in SQL Server

Asked

Viewed 669 times

1

I am trying to set the variable with a query, select only works this way using WITH, but it is giving error when I will assign the value in the variable.

The code I’m trying to: 

  DECLARE @contador INT, @empresas INT, @id_empresa INT
  SET @contador = 1
  SET @empresas = (SELECT COUNT(ID_Pessoa) FROM Empresa)


  WHILE (@contador <= @empresas)
  BEGIN

  SET @id_empresa = with CTE_R as
  (SELECT e.ID_Pessoa, ROW_NUMBER() OVER(ORDER BY ID_Pessoa) as RowNum FROM                         Empresa e (NOLOCK)) 
  select ID_Pessoa from CTE_R where RowNum = 1

  INSERT INTO Produto_Lista_Empresa (ID_Produto_Lista, ID_Empresa)
  SELECT ID, @id_empresa FROM Produto_Lista

  SET @contador = @contador + 1
  CONTINUE;
  END

It is giving error when assigning the value of the variable @id_company

sql sql-server

  • Where is giving the error and which error appears?

  • is giving error when assigning value to @id_company variable, error is: "Incorrect syntax near the keyword 'with'."

1 answer

3


WITH CTE_R AS (
  SELECT e.ID_Pessoa,
         ROW_NUMBER() OVER(ORDER BY ID_Pessoa) AS RowNum
    FROM Empresa e WITH(NOLOCK)
)
SELECT @id_empresa = ID_Pessoa
  FROM CTE_R
 WHERE RowNum = 1

But in reality you have another way to achieve that value:

SELECT TOP 1 @id_empresa = e.ID_Pessoa
  FROM Empresa e WITH(NOLOCK)
 ORDER BY ID_Pessoa

Observing: Your logic seems somewhat mistaken. The best way to do this insertion would not be a LOOP. You can achieve the same result with the following INSERT:

INSERT INTO produto_lista_empresa(id_produto_lista,
                                  id_empresa)
SELECT pl.id,
       e.id_pessoa
  FROM empresa e
 CROSS JOIN produto_lista pl

Browser other questions tagged sql sql-server

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