将结果集存储为过程中的变量 [英] Storing result set as a variable in a procedure

问题描述

HI，



我有一个存储过程，我使用插入选择语句来插入特定记录。

I have a stored procedure where i am using a Insert Select Statement to insert specific records.

Insert into Test.dbo.G_Record 
Select * from Test.dbo.InformationCurrent c
INNER JOIN Test.dbo.VendorInformation pr  (NOLOCK) ON c.MarketNumber = pr.MarketNumber AND c.FeeSchedule = pr.VendorFeeSchedule AND c.Specialty = pr.Specialty	
INNER JOIN Test.dbo.Translation s  (NOLOCK) ON c.Specialty = s.NMDB_Specialty
Where 
(c.ClientID = '287' AND pr.VendorProductIndicator IN ('SX','SY','SZ'))
OR  ( c.ClientID = '559' AND pr.VendorProductIndicator = 'D7' ) 

现在我有一个新表Temp（ClientID，ProductIndicator），数据如下



ClientID ProductIndicator

288 SX

288 SI

256 SJ

256 SY



现在我必须将这些值添加到上面的insert select语句中，其中的条件如下面的语句。

（clientID ='288'和ProductIndicator IN（'SX'，'SI'））或

（clientID ='256'和ProductIndicator IN（'SJ'， 'SY'））



类似于上面的行必须在没有硬编码的情况下自动添加到新Temp表中任意数量记录的现有Insert select查询中。



提前谢谢...

Now i have a new table Temp(ClientID,ProductIndicator) with data as follows

ClientID ProductIndicator
288 SX
288 SI
256 SJ
256 SY

Now i have to add these values to the above insert select statement's where condition like below statements.
(clientID='288' AND ProductIndicator IN('SX','SI')) OR
(clientID='256' AND ProductIndicator IN('SJ','SY'))

As similar to above lines have to be added to the exisiting Insert select query for any number of records in the new Temp table automatically without hard coding.

Thanks in advance...

推荐答案

我会传出一个XML，例如：< br $>

I would pass up an XML such as:

<clauses>
  <c clientid="287">
    <vpi product="SX" />
    <vpi product="SY" />
    <vpi product="SZ" />
  </c>
  <c clientid="559">
    <vpi product="D7" />
  </c>
  <c clientid="288">
    <vpi product="SX" />
    <vpi product="SI" />
  </c>

</clauses>

然后您可以选择此结构来生成表示这些值的表格：



他是从XML中选择数据集的示例



从SQL Server 2008中的XML字段中选择值 [ ^ ]

You can then select against this structure to product a table representing these values:

He're an example on selecting a data set from XML

Select values from XML field in SQL Server 2008[^]

Client Product
287    SX
287    SY
287    SZ
559    D7
288    SX
287    SI

然后 INNER JOIN 这将产品数据库过滤掉所需的产品。

You then INNER JOIN this to your product database to filter out the desired products.

这篇关于将结果集存储为过程中的变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！