Solved

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

Posted on 2009-05-11
9
539 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
  • 5
  • 4
9 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
Comment Utility
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
Comment Utility
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 500 total points
Comment Utility
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
 

Author Comment

by:fullbugg
Comment Utility
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 39

Expert Comment

by:BrandonGalderisi
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Not a problem... I am new to SPs, Is this possible to on the path that we have started?
0
 

Author Comment

by:fullbugg
Comment Utility
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

771 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

7 Experts available now in Live!

Get 1:1 Help Now