?
Solved

Insert comma and Hyphen seperated string into table via a Stored Procedure

Posted on 2009-05-11
9
Medium Priority
?
563 Views
Last Modified: 2012-05-06
Hello,
I have a delimma... I have to insert a comma and hyphen seperated string into a table within a stored procedure.

The string is:
"CODE0-1,CODE2-0,CODE3-1"

CODE0 and the 1 values will be an insert
CODE1 and the 0 values will be an insert
and so on...

I know that this will have to be done within a While Loop but I don't know if I would be better off sticking this into a temp table then looping through the temp to do the inserts or what.

I am up open for any suggestions... Please help
0
Comment
Question by:fullbugg
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24356503
Here is a function that I wrote to split a delimited string into a table.

http://sqlservernation.com/blogs/howtos/archive/2009/03/07/converting-a-delimited-string-into-a-table.aspx

You can use it to split on the comma, then use charindex to split CODE1 from the 1, CODE2 from the 0, etc.
0
 

Author Comment

by:fullbugg
ID: 24357630
Thanks for the reply,
I have a SP that I am working with now that does what your function does. I was hoping to do something internal without having to leave the originating SP. If you could explain further that would be helpful.
ALTER PROCEDURE [dbo].[sp_AddListToTempTable] 
    @cslist VARCHAR(8000)
    
AS    
BEGIN 
    DECLARE @spot SMALLINT, @str VARCHAR(8000)
    CREATE TABLE #vals (CODE NVARCHAR(100))
    WHILE @cslist <> '' 
    BEGIN 
        SET @spot = CHARINDEX(',', @cslist) 
        IF @spot>0 
            BEGIN 
                SET @str = LEFT(@cslist, @spot-1)
                SET @cslist = RIGHT(@cslist, LEN(@cslist)-@spot) 
            END 
        ELSE 
            BEGIN 
                SET @str = @cslist 
                SET @cslist = '' 
            END 
        INSERT INTO #vals (CODE) VALUES (@str)
    END 
    SELECT * FROM #vals 
    DROP TABLE #vals
END
-- USAGE
-- EXEC sp_AddListToTempTable 'CODE0-1,CODE1-0,CODE2-0,CODE3-0,CODE4-0,CODE5-0'

Open in new window

0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 2000 total points
ID: 24357696
The downside of the stored procedure, is that you have to do something with the values.  In your case, you are selecting them at the end of the procedure, so you have to create a temp table in the parent procedure to capture the values.


create table #SomeThing
(tValue varchar(100))
insert into #something
exec sp_addlisttotemptable @SomeValue

select * from #something
drop table #something

with the table valued function, you can just select from it.

select * from dbo.fn_DelimitedToTable(@Something,',')

To expand upon your original requirement.

select left(theValue,charindex('-',theValue)-1)
,right(theValue,len(theValue)-(charindex('-',theValue)))
from dbo.fn_DelimitedToTable(@Something,',')

Open in new window

0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:fullbugg
ID: 24358612
Ok... I am with you now... However doing it like this I am uncertain how to loop through the results in the calling SP.
Do I need to use @@RowCount?
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24358685
So you need to loop through each and call a procedure? based upon each name/value pair?  I thought you just needed to split the string and then insert the values?

If you just need to insert the values....

INSERT INTO SOMETABLE(Field1,Field2)
select left(theValue,charindex('-',theValue)-1)
         ,right(theValue,len(theValue)-(charindex('-',theValue)))
from dbo.fn_DelimitedToTable(@Something,',')

If that's not what you need, you'll have to expand.  I see no reason to do a while loop.
0
 

Author Comment

by:fullbugg
ID: 24359048
I understand... I am not explaining very well. By the way, thanks for all your time and energy on this:
There are additional values that must accompany the insert of the two fields that are being parsed appart.
One is generated by the call of another SP to get a unique row identifier.
Here is what I have got:

Table:
MovementCodeID    MovementCode   Code         MAIN       DtTme
002200000001        112200000001    CODE0         0            2009-01-01 00:00:00

The MovementCodeID is generated in the loop statement.

I hope that this helps...

Thanks again for your time...
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

0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24361191
Well perhaps you should accept this answer, as it resolves the asked question, of being able to insert a comma/hyphen delimited list.  Then open a new question to re-factor sp_getnextkey to be set based instead of row based.

If that's not possible, LMK.
0
 

Author Comment

by:fullbugg
ID: 24363566
Not a problem... I am new to SPs, Is this possible to on the path that we have started?
0
 

Author Comment

by:fullbugg
ID: 24363622
Thanks Brandon, You have been a tremendous help...
The new thread is here.

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_24400923.html
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
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.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

649 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