Split Function 1

[Naoise]

I have a table tblA

tblA
===================
fldA | fldB | fldC

and a stored procedure spAddData which receives a list as follows 1,abc,100¬1,def,101¬1,ghi,103

Basically the row delimiter is '¬' and field delimiter is ','

How could I best go about inserting a delimited string like this into tblA? No Split UDF function I have seen on the web has been helpful yet. Thanks in advance for replies.

 

[gmmastros]

What follows pertains to Microsoft SQL Server.

The problem is that SPLIT is a table valued function (because it returns multiple records and/or fields) vs. a scalar valued function which returns just a single value (eventhough that value can be any data type). The problem with table valued functions is that you cannot apply them to multiple records with a single statement. Usually, this requires the use of a cursor or a loop.

Do you have the ability to modify how the data is sent in? If you sent XML data to the stored procedure instead, this functionality would be a lot easier. Let me show you how.

First, the stored procedure:

Alter Procedure TestXMLInput
	@XML Text
As
SET NOCOUNT ON

Declare @iDoc Integer
Exec sp_xml_preparedocument @iDoc OUTPUT, @XML

[green]-- uncomment the next 2 lines to insert into table
--Insert
--Into    tblA(fldA, fldB, fldC)[/green]
Select	fldA, fldB, fldC
From	OpenXML(@iDoc, '//root/data', 1)
With	(
        fldA Int,
        fldB Varchar(10),
        fldC int
        )

You can test this function, like so...

TestXMLInput 
[red]'<root>
  <data fldA="1" fldB="abc" fldC="100"/>
  <data fldA="1" fldB="def" fldC="101"/>
  <data fldA="1" fldB="ghi" fldC="103"/>
</root>'[/red]

The XML does not require any formatting (carriage returns or extra spaces). I did it this way to make it easier to see how the data was put together.

I hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

Has this helped?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Naoise]

I couldn't fit it into my solution currently but I have saved it for future reference, so in that respect it has helped. Thanks again.

 

[gmmastros]

Do you have a solution to your problem, or are you still looking for advice?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Naoise]

I orchestrated a different solution in the business layer rather than the data access layer so all is good. Thanks for following up though