executing a stored proceedure using a list of items

Posted on 2009-04-15
Last Modified: 2012-05-06
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?  
Question by:NetserMA
    LVL 60

    Expert Comment

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

    Expert Comment

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


    Author Comment

    Each item in the list is fixed 10 characters

    Author Comment

    Ok   i'm confused.    Where do I put the sp in the coding and were is the list inserted?  

    Sorry to be so dumb!
    LVL 16

    Accepted Solution

    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)


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    how to add IIS SMTP to handle application/Scanner relays into office 365.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now