Storing result set as a variable in a procedure

• anupama_bommaraju 6767

  Newbie

  Points: 9

  More actions

  May 28, 2014 at 7:53 am

  #286967

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

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  May 28, 2014 at 7:57 am

  #1717023

  Hi and welcome to the forums. In order to help we will need a few things:

  1. Sample DDL in the form of CREATE TABLE statements

  2. Sample data in the form of INSERT INTO statements

  3. Expected results based on the sample data

  Please take a few minutes and read the first article in my signature for best practices when posting questions.

  Also, why the NOLOCK hints? Are you aware that you can and will get missing and/or duplicate data with those hints?

  http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/10/1280.aspx

  http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx

  http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/[/url]

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• weitzera

  Hall of Fame

  Points: 3442

  More actions

  May 28, 2014 at 11:54 am

  #1717112

  Also, your question doesn't match the title of your post. did you miss a part of the question?

  ---

  
  

  Dan Guzman - Not the MVP (7/22/2010)

  ---

  All questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time.'