Question for T-sql experts - how to mimic a user defined function that returns multiple values

Question

Question for T-sql experts - how to mimic a user defined function that returns multiple values

Sylvia Moestl Vasilik

Old Hand

Points: 384
More actions
April 7, 2008 at 11:01 pm

#76949

Hello all,
I'm doing a funky file import in SQL Server 2000, where a certain field has different formats of data in it, depending on what record type it is. I have some code that parses out a set of values from a DataToBeParsed field, based on a RecordType field, using substrings. I need to encapsulate this code for use in multiple procedures. I looked at all the user defined functions, but they don't seem to do what I want. The only thing that looks hopeful is the cross-apply in 2005.
Unfortunately I'm using SQL Server 2000, so that's not an option.
The update section at the bottom is what I want to encapsulate.
Does anyone have any ideas?
thanks,
Sylvia
Create Table DataLoadStaging (
RecordType varchar(20) not null
,DataToBeParsed varchar(1000) not null
,DataField1 varchar(10) null
,DataField2 varchar(10) null
,DataField3 varchar(10) null
,DataField4 varchar(10) null
)
insert into DataLoadStaging (RecordType, DataToBeParsed) values ('RecordType A', 'abc132 6623 ')
insert into DataLoadStaging (RecordType, DataToBeParsed) values ('RecordType A', 'bwe132 9284 ')
insert into DataLoadStaging (RecordType, DataToBeParsed) values ('RecordType A', 'irt132 0911 ')
insert into DataLoadStaging (RecordType, DataToBeParsed) values ('RecordType B', ' 0092 yyyy ')
insert into DataLoadStaging (RecordType, DataToBeParsed) values ('RecordType B', ' 8782 wwww ')
insert into DataLoadStaging (RecordType, DataToBeParsed) values ('RecordType B', ' 1193 yyyy ')
insert into DataLoadStaging (RecordType, DataToBeParsed) values ('RecordType C', '4 2341 oooa ')
insert into DataLoadStaging (RecordType, DataToBeParsed) values ('RecordType C', '59 9841 uuua ')
insert into DataLoadStaging (RecordType, DataToBeParsed) values ('RecordType C', '12 2231 aaaa ')
update DataLoadStaging
set
DataField1 =
case
when RecordType = 'RecordType A' then substring(DataToBeParsed, 1, 6)
when RecordType = 'RecordType B' then null
when RecordType = 'RecordType C' then null
end
,DataField2 =
case
when RecordType = 'RecordType A' then substring(DataToBeParsed, 13, 4)
when RecordType = 'RecordType B' then substring(DataToBeParsed, 4, 4)
when RecordType = 'RecordType C' then substring(DataToBeParsed, 9, 4)
end
,DataField3 =
case
when RecordType = 'RecordType A' then null
when RecordType = 'RecordType B' then substring(DataToBeParsed, 16, 4)
when RecordType = 'RecordType C' then substring(DataToBeParsed, 17, 4)
end
,DataField4 =
case
when RecordType = 'RecordType A' then null
when RecordType = 'RecordType B' then null
when RecordType = 'RecordType C' then substring(DataToBeParsed, 1, 2)
end
select * from DataLoadStaging

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jacob Luebbers SSCarpal Tunnel Points: 4834 More actions · Answer 1

I'd create 4 seperate inline UDFs, one for each DataField column. Each function would take in two parameters (RecordType & DataToBeParsed) and return a varchar(10) value, and would encapsulate one of your 4 CASE statements respectively.

Regards,

Jacob

Sylvia Moestl Vasilik Old Hand Points: 384 More actions · Answer 2

Thanks, that's a good idea. As a matter of fact, it's made me realize I might be able to have it all in one function - just by having also a "FieldToReturn" type parameter. Because I actually don't just have 4 data fields, I have about 16.

Sergiy SSC Guru Points: 110208 More actions · Answer 3

Sylvia Moestl Vasilik (4/10/2008)
Thanks, that's a good idea. As a matter of fact, it's made me realize I might be able to have it all in one function - just by having also a "FieldToReturn" type parameter. Because I actually don't just have 4 data fields, I have about 16.

Then you're gonna have 16 subqueries retriving data for each row.

16 times worse than a cursor.

_____________
Code for TallyGenerator

Jacob Luebbers SSCarpal Tunnel Points: 4834 More actions · Answer 4

@sergiy: No, if these are inline UDFs they should be expanded in place, similar to a C macro and shouldn't incur a RBAR-like performance hit.

Regards,

Jacob

Sergiy SSC Guru Points: 110208 More actions · Answer 5

Can you please post an example of inline UDF which will select a table field depending on a parameter and and SELECT statement using it which will not cause RBAR processing?

_____________
Code for TallyGenerator

Jacob Luebbers SSCarpal Tunnel Points: 4834 More actions · Answer 6

@sergiy: No, you're right - I can't :blush:

Just did a quick test using both inline and scalar UDFs for some test data based on the OP's sample. The inline form was the worst by many orders of magnitude, and the scalar version was about 1 order of magnitude worse than the non-UDF version.

I'll just go off and hide for a while...

Regards,

Jacob