Click here to Skip to main content
14,635,966 members
Rate this:
Please Sign up or sign in to vote.
See more:
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' ) 


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...
Posted

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

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>


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


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

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100