asked on

Insert additional values along with values pulled from a function

This is a continuation from a previous post
https://www.experts-exchange.com/questions/24398538/Insert-comma-and-Hyphen-seperated-string-into-table-via-a-Stored-Procedure.html?anchorAnswerId=24363566#a24363566

I need to use the results of the first post to and add values generated from another SP to insert multiple rows into another table.

DECLARE @count INT
DECLARE @LoopCount INT
DECLARE @fp VARCHAR(30)
DECLARE @Site VARCHAR(30)
DECLARE @MovementID VARCHAR(30)
DECLARE @MovementCodeID VARCHAR(30)
 
SET @Site = 'TestSite'
 
SELECT LEFT(theValue,CHARINDEX('-',theValue)-1) AS CODE
,RIGHT(theValue,LEN(theValue)-(CHARINDEX('-',theValue))) AS MAIN
FROM dbo.fn_DelimitedToTable('CODE0-1,CODE1-0,CODE2-0,CODE3-0,CODE4-0,CODE5-0',',')
 
SET @count = @@ROWCOUNT
SET @LoopCount = 1
SELECT @COUNT
WHILE @LoopCount <= @count
	BEGIN
		EXEC sp_GetNextKey @Site,'ItemMovement',@fp OUTPUT -- Get Unique Key for Row
			IF @fp IS NULL       
				BEGIN
					RAISERROR 50010 'Unable to get Movement ID - 50010'
					RETURN  
				END
			SET @MovementCodeID = @fp
		INSERT INTO tablename (MovementCodeID,MovementCode,Code,Main,DtTme) 
				VALUES (@MovementCodeID,@MovementCode,CODE,MAIN,GETDATE())
		IF @@ROWCOUNT <> 1 
			BEGIN
				RAISERROR 50020 'Unable to Insert Record - 50020'
				RETURN  
			END
		SET @LoopCount = @LoopCount + 1
	END

Open in new window

ASKER CERTIFIED SOLUTION

GuitarRich

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bull_rider

Do you want this to be done in the code you have posted in this thread? If thats the case you have to call the other SP here and assign it to new variables and then insert into the required table just after the current insert statement. Am I interpreting it right?

fullbugg

ASKER

Thanks for the replies...
GuitarRich, I am getting an error on the "Incorrect syntax near the keyword 'CURSOR'."
Does the SELECT need to be wrapped in parenthesis?

GuitarRich

oops - sorry in my haste I mixed up the declare - swap the word CURSOR and curDelimetedData around like this:

declare cursor curDelimetedData for
	SELECT LEFT(theValue,CHARINDEX('-',theValue)-1) AS CODE
	,RIGHT(theValue,LEN(theValue)-(CHARINDEX('-',theValue))) AS MAIN
	FROM dbo.fn_DelimitedToTable('CODE0-1,CODE1-0,CODE2-0,CODE3-0,CODE4-0,CODE5-0',',')

Open in new window

fullbugg

ASKER

GuitarRich... You are tremendous. With these posts I am able to finish the project on time.

THANKS!!!

fullbugg

ASKER

This is EXACTLY what was needed. Thanks...

BrandonGalderisi

You DON'T want to use cursors. The whole purpose of me suggesting you open a new question was to avoid doing any looping and to rewrite sp_GetNextKey to be set based.

fullbugg

ASKER

The difficulty in the rewrite of the sp_GetNextKey is that many areas of the program uses the SP to get keys for a multitude of tables.

BrandonGalderisi

Ok. Perhaps rewrite wasn't the best use of words. But we could, and still can, probably extract the logic and make it compatible for a set based operation instead of RBAR procedure based.