通过存储过程中的不同列值从单个表中选择多个表

您最好的选择不是在Proc中执行此操作,而是在客户机代码中执行此操作。

1. 编写一个返回 与MyTable不同的MYID)
2. 在你的应用程序中,通过每个 它接受MyID值并返回

选择不同的MyId 从MyTable

您可以创建一个循环结构,该结构通过主表ID的子集进行迭代,为每组表生成一个单独的结果集。这很难看,但希望这只是一些特别的事情,你想摆脱的方式,希望永远不要再处理。

DECLARE @Entities TABLE
(
     ID int IDENTITY (1, 1) PRIMARY KEY NOT NULL,
     MyID int,
     MyOtherData varchar(100)
)

INSERT INTO @Entities (MyID, MyOtherData)
SELECT MyID, MyOtherData
FROM TheMainTable
ORDER BY MyID, MyOtherData

DECLARE @Count int
DECLARE @Max int

SET @Count = 1
SELECT @Max = COUNT(ID) FROM @Entities

DECLARE @MyID_Current int

WHILE (@Count <= @Max)
BEGIN

     SELECT @MyID_Current = MyID
     FROM @Entities
     WHERE ID = @Count

     -- Output each individual result set.
     SELECT MyID, MyOtherData
     FROM @Entities
     WHERE MyID = @MyID_Current

     SET @Count = @Count + 1

END

对于更丑陋的东西,您可以在每次迭代时创建或选择数据库中一个新的、唯一命名的表,并将特定MyID的记录插入该表,而不只是大量生成单独的结果集。

DECLARE @Entities TABLE
(
 ID int IDENTITY (1, 1) PRIMARY KEY NOT NULL,
 MyID int,
 MyOtherData varchar(100)
)

INSERT INTO @Entities (MyID, MyOtherData)
SELECT MyID, MyOtherData
FROM TheMainTable
ORDER BY MyID, MyOtherData

DECLARE @Count int
DECLARE @Max int

SET @Count = 1
SELECT @Max = COUNT(ID) FROM @Entities

DECLARE @MyID_Current int

WHILE (@Count <= @Max)
BEGIN

 SELECT @MyID_Current = MyID
 FROM @Entities
 WHERE ID = @Count

 -- Output each individual result set.
 SELECT MyID, MyOtherData
 FROM @Entities
 WHERE MyID = @MyID_Current

 SET @Count = @Count + 1

END