如何使用SQLServer逐个将行提取到变量中 [英] How to fetch one by one row into a variable using SQLServer

问题描述

我想将一个一个值存储到一个表中的变量中，该表应该位于SQLServer中的SP内部。



例如

I want to store one by one value into a variable from a table which should be inside an SP in SQLServer.

e.g.

    declare @GetStudentName nvarchar(128)
set @GetStudentName = (select StudentName from StudentProfile where session = 'ThisSession')

print @GetStudentName
the error shows 
"Msg 512, Level 16, State 1, Line 2
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."

执行此语句后我有一个学生姓名列表，然后我想逐个检查。请尽快帮我解决这个问题。

after execute this statement I have got a list of student name then I want to check one by one. Please help me out from this problem as soon as possible.

推荐答案

你可以使用一个游标：

You could just use a CURSOR:

DECLARE cur CURSOR FOR SELECT StudentName FROM StudentProfile where session = 'ThisSession'
OPEN cur
FETCH NEXT FROM cur INTO @StudentName
WHILE @@FETCH_STATUS = 0 BEGIN
    PRINT @StudentName
    FETCH NEXT FROM cur INTO @StudentName
END
CLOSE cur
DEALLOCATE cur

除非你真的需要逐个遍历行，否则我建议尝试使用基于集合的操作。



SQL是一种优化用于操作和处理数据集的语言。从性能的角度来看，这始终是最快的方式，但从编码的角度来看，设置操作确实很有效。



例如，如果你需要给向所有在某个部门工作的人提高10％，而不是循环遍历行，计算新工资并更新行，您可以使用单个语句，例如

Unless you really need to go through the rows one-by-one I really advise to try to use set based operations.

SQL is a language optimized for manipulating and handling sets of data. From a performance point of view this is always the fastest way but also from the coding point of view, set operations are really effective.

For example if you need to give a 10% raise to all people working in a certain department, instead of looping through the rows, calculating the new salary and updating the row you could use a single statement like

UPDATE person
SET Salary = Salary * 1.1
WHERE Department = ...

如果为作业创建单个语句变得复杂，则可以始终使用临时表 [ ^ ]，表变量 [ ^ ]等等。

If creating a single statement for the job becomes to complex you can always utilize temporary tables[^], table variables[^] and so on.

这篇关于如何使用SQLServer逐个将行提取到变量中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！