语法将两个存储过程合并为一个

Question

 SELECT *
 FROM   [Live].[Test]
 WHERE  [_fk_RoadMapKey] = @_fk_RoadMapKey
 SELECT @TestKey = [TestKey]
 FROM   [Live].[Test]
 WHERE  [_fk_RoadMapKey] = @_fk_RoadMapKey
 SELECT *
 FROM   [Live].[SetofTestGrades]
 WHERE  [_fk_TestKey] = @TestKey

的前两个语句，我们是否可以在指定testkey

的同时获得所有的test ?

SELECT * ,@TestKey = [TestKey] FROM [Live].[Test]
    WHERE [_fk_RoadMapKey] = @_fk_RoadMapKey

Answer 1

你不能给一个变量分配数据，也不能使用单个select语句返回数据，如果你正在使用@TestKey从另一个表中检索值，那么你可以跳过变量并使用join从另一个表中获取数据，试试这个

 SELECT *
 FROM   [Live].[Test]
 WHERE  [_fk_RoadMapKey] = @_fk_RoadMapKey
 SELECT S.*
 FROM  [Live].[SetofTestGrades] S 
 INNER JOIN [Live].[Test] T ON S.[_fk_TestKey]  = T.[TestKey]
 WHERE  t.[_fk_RoadMapKey] = @_fk_RoadMapKey