c# - 如何将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))

我有一个查询，它返回教职员工的TeachingID，它是教职员工教授的类(class)的ID。

我的问题是:

1. 如何存储这些值，因为它返回超过 1 个值，我可以在 sql server 或 c# 中使用数组吗？

2. 然后我想获取这些值并将其传递给其他查询，如下所示:

(从考试 WHERE 中选择考试日期(ExamID = @GetExamID)

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

我可以使用 foreach 或 array 吗？我正在使用 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 个查询，第一个查询将获得教学 ID(1 个值)第二个将获取 courseid(大于 1 的值)然后接下来的两个将获取 courseid 并获取考试日期和时间然后我想检查最后一个查询是否有结果

Answer 1

你可以把它写成

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

exec @query 

并从 C# 传递 @GetExamID 作为逗号分隔的字符串 1,2,4,5,......

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

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