如何将sql查询存储在变量中

Question

SELECT Staff_Teaching.TeachingID FROM   Staff_Teaching INNER JOIN
          Staff ON Staff_Teaching.StaffID = Staff.StaffID WHERE        (Staff.StaffID = @StaffID) AND (Staff_Teaching.SemesterID = @SemesterID))

我有一个查询，它返回staff TeachingID，它是工作人员教授的课程的id。

我的问题是：

1. 如何存储这些值，因为它返回多个值，所以可以在sql server或c#中使用数组吗？
2. 然后，我想获取这些值并将其传递给其他查询，如下所示：

(从考试中选择Exam.Date (ExamID = @GetExamID)

`@GetExamID` is where I want to pass these values to be checked 

我可以使用foreach或数组吗？我使用的是asp.net (C#)

这就是我想要做的

BEGIN TRANSACTION
SET @GetTeachingID =(SELECT        Staff_Teaching.TeachingID
FROM          Staff_Teaching INNER JOIN
              Staff ON Staff_Teaching.StaffID = Staff.StaffID
WHERE        (Staff.StaffID = @StaffID) AND (Staff_Teaching.SemesterID = @SemesterID))
COMMIT 

BEGIN TRANSACTION
SELECT  Course_Department.CourseID
FROM            Course_Department INNER JOIN
                         Exam ON Course_Department.CourseID = Exam.ExamID INNER JOIN
                         ExamSchedule ON Exam.ExamID = ExamSchedule.ExamID INNER JOIN
                         Staff_Teaching ON Course_Department.TeachingID = Staff_Teaching.TeachingID
WHERE        (Staff_Teaching.TeachingID = @GetTeachingID)

COMMIT 


BEGIN TRANSACTION
SET @GetExamDate=(SELECT Exam.Date  
FROM Exam
WHERE (ExamID = @GetExamID))
COMMIT 

BEGIN TRANSACTION
SET @GetExamTime=(SELECT Exam.BegainTime  
FROM Exam
WHERE (ExamID = @GetExamID))
COMMIT 

BEGIN TRANSACTION
SELECT        ExamScheduleID
FROM          Exam INNER JOIN
              ExamSchedule ON Exam.ExamID = ExamSchedule.ExamID
WHERE        (ExamSchedule.ExamScheduleID = @ExamScheduleID) AND (Exam.Date LIKE @GetExamDate) AND (Exam.BegainTime<=@GetExamTime)
COMMIT 



END

我有5个查询，第一个查询将得到教学信息(1个值)，第二个查询将得到课程(大于1个值)，接下来两个查询将获得考试日期和时间，然后检查最后一个查询是否有任何结果。

Answer 1

SQL Server中没有类似的数组。您可以使用#temp表临时存储此表。

SELECT Staff_Teaching.TeachingID
INTO #Temp
Staff_Teaching INNER JOIN
      Staff ON Staff_Teaching.StaffID = Staff.StaffID WHERE                    
     (Staff.StaffID = @StaffID) AND (Staff_Teaching.SemesterID = @SemesterID))

临时表

把它当作

SELECT Exam.Date  FROM Exam WHERE (ExamID IN SELECT * FROM #Temp)

Answer 2

你可以把它写成

declare @query = '(SELECT Exam.Date  FROM Exam WHERE (ExamID IN ('+@GetExamID+') )'

exec @query 

并将@GetExamID从C#中作为逗号分隔字符串1,2,4,5，.

可以使用以下方法将int数组转换为逗号分隔的字符串

var result = string.Join(",", arr);