Save the value of a column in a variable using a WHILE

Question

Save the value of a column in a variable using a WHILE

Navigation

#1 by (1 votes)
#2 by (0 votes)

0

I'm trying to save a value in a variable.

I create the table and insert records.

CREATE TABLE #TEMP (IDSOL INT not null PRIMARY KEY ,USUARIO VARCHAR(50) ,LastName varchar(255) NOT NULL,FirstName varchar(255),Age int, )

INSERT INTO #TEMP 
VALUES(1,'ADMIN','RODRIGUEZ','LOPEZ',20)
INSERT INTO #TEMP 
VALUES(2,'ADMIN','YAÑEZ','CARROL',20)
INSERT INTO #TEMP 
VALUES(3,'ADMIN','MONS','JAFET',20)

Then I need to set the column IDSOL in a variable so I'm using a WHILE this is what I have.

DECLARE @I INT
DECLARE @J INT
DECLARE @VALOR_IDSOL 

SET @I =  (SELECT COUNT(*) FROM #TEMP WHERE USUARIO = 'ADMIN')
SET @J = 1

WHILE @I >= @J
BEGIN   

    SET @VALOR_IDSOL = (SELECT IDSOL FROM #TEMP WHERE USUARIO = 'ADMIN')

    SET @J = @J + 1

END

I get the number of records in the query and then I pass the same number of records for the WHILE I want to get the value IDSOL of the first record and in the next round the second record and in the third round the same and that way until it's over, someone can give me some idea.

sql sql-server

asked by ARR 25.01.2018 в 20:10

source

2 answers

0

According to what was said in the comments, for each value obtained, another sp is executed and this value is passed to it

  DECLARE @I INT
    DECLARE @J INT
    DECLARE @VALOR_IDSOL 

    SET @I =  (SELECT COUNT(*) FROM #TEMP WHERE USUARIO = 'ADMIN')
    SET @J = 1

    WHILE @I >= @J
    BEGIN   

        SET @VALOR_IDSOL = (SELECT IDSOL FROM #TEMP WHERE USUARIO = 'ADMIN')

        /*Ejecuta el otro sp pasando el valor*/
        Exec sp_quenoestaenelcodigo(@VALOR_IDSOL)
         SET @J = @J + 1

    END

answered by 25.01.2018 в 20:34

Given a sentence ending in point, count how many words begin with the letter S [closed] DAO / VO Custom?

score 1 · Accepted Answer

For what you understand, you are looking to iterate over each record in the #temp table and access each id within the table to do what you need with that id, which you can achieve with a cursor in the following way:

DECLARE @IDSOL_REGISTRO INT

DECLARE CUR CURSOR FOR
SELECT IDSOL FROM #TEMP
OPEN CUR
FETCH NEXT FROM CUR INTO @IDSOL_REGISTRO
WHILE @@FETCH_STATUS = 0
  BEGIN
    --@IDSOL_REGISTRO CONTIENE EL VALOR DEL REGISTRO ACTUAL EL CUAL PUEDES USAR EN LO QUE NECESITES
    --AQUI VA LO QUE NECESITES HACER CON EL IDSOL DEL REGISTRO
    FETCH NEXT FROM CUR INTO @IDSOL_REGISTRO
  END
CLOSE CUR
DEALLOCATE CUR