Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 232
  • Last Modified:

executing a stored proceedure using a list of items

I have a stored proceedure that our vendor says we need to run on some records with errors.   As a matter of fact 191 records.   Is there a way to execute a stored proceedure from a list.  Below is the stored proceedure and i have to enter in the account number is the ###### area.  
sprecalcpeopleorgbalance 'P#########'

I have to do this is a test environment and then on the live database.    Any suggestions?  
Thanks!
0
NetserMA
Asked:
NetserMA
  • 2
  • 2
1 Solution
 
chapmandewCommented:
No, not really with executing a stored proc.  You'll probably have to use a cursor to loop through the values and pass them to the proc as params.
0
 
brad2575Commented:
Is the list fixed or will it change as champ said above you could use a curser or there is a way to "fake" an array of values and loop through them as well (just used this and it works good).

the "fake" array code is below, and you would just put your SP in the midle
Faking the array code is here:
	DECLARE @LookupTypeList VARCHAR(5000)
	DECLARE @LookupTypeSingle VARCHAR(500)
 
	SET @LookupTypeList = 'ClaimStatusMatrix,TaskMapping,ClaimOwnerMatrix'
 
 
	-- loop through the array of values
	WHILE LEN( @LookupTypeList ) > 0 BEGIN 
		-- *********************************************************
		-- ********** CODE TO Get Each Array Item value ************
		-- *********************************************************
			-- check to see if there are more then 1 value left
			IF CHARINDEX( ',', @LookupTypeList ) > 0 				
				-- select the next value into the single array value and set the list to remove the value we just retrieved for the single value
				SELECT @LookupTypeSingle = LEFT(@LookupTypeList, CHARINDEX(',', @LookupTypeList) - 1 ),
					@LookupTypeList = RIGHT(@LookupTypeList, LEN(@LookupTypeList) - CHARINDEX(',', @LookupTypeList) ) 
			ELSE
				-- there is only 1 value left so set it to the final item in the list
				SELECT @LookupTypeSingle = @LookupTypeList, @LookupTypeList = SPACE(0)		
		-- *********************************************************
		-- ********** END CODE TO Get Each Array Item value ********
		-- *********************************************************			
		
   -- put code here to run for each loop value
 
	END -- end of while loop

Open in new window

0
 
NetserMAAuthor Commented:
Each item in the list is fixed 10 characters
0
 
NetserMAAuthor Commented:
Ok   i'm confused.    Where do I put the sp in the coding and were is the list inserted?  

Sorry to be so dumb!
0
 
brad2575Commented:
the list of items goes into this variable:
@LookupTypeList =


and you put the SP where it says:
 -- put code here to run for each loop value
(towards the bottom)

0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now