CODEWITHSUNDEEP
sqlsql-serverloops
Isharehappy K
Isharehappy K

Reputation: 3

SQL Looping temp table and reading data

I have following script to create temp data

DECLARE @Name NVARCHAR(100), @Marks INT
DECLARE @MYTABLE TABLE
(
   [Name][nvarchar](100) NULL,
   [Marks][INT] NULL
)

INSERT INTO @MYTABLE ([Name],[Marks]) VALUES ('Mark',50);
INSERT INTO @MYTABLE ([Name],[Marks]) VALUES ('Steve',50);
INSERT INTO @MYTABLE ([Name],[Marks]) VALUES ('Don',50);

Now I want loop it, as shown in below script

SELECT @MaxPK = MAX(PK) from @MYTABLE
WHILE @PK <= @MaxPK
BEGIN
 SET @Name =  SELECT Name from @MYTABLE 
 SET @Marks =  SELECT Marks from @MYTABLE   
 print @Name
 print @Marks
 SET @PK = @PK + 1
END

But I get error near SELECT statement.

"Incorrect syntax near the keyword SELECT"!

Upvotes: 0

Views: 3868

Answers (2)

TechDo
TechDo

Reputation: 18629

Please try below while loop:

WHILE @PK <= @MaxPK
BEGIN
 SELECT @Name = Name, @Marks = Marks from @MYTABLE
 print @Name
 print @Marks
END

Note: I guess, a where condition is required in select statement in order to print all data.

Upvotes: 1

David S&#246;derlund
David S&#246;derlund

Reputation: 988

The two rows where you set the variables can be put together like this. That way you will only scan the table once.

SELECT @Name = Name, @Marks = Marks FROM @MYTABLE

Just know that the row chosen to be put in your variables is completely arbitary (and will probably be the same row every time) unless you add a whereclause.

Upvotes: 1

Related Questions