June 27, 2013 at 1:43 pm
Hi all,
I have a top level sproc that returns x number of records. Each record has a field called CategoryID that stores a comma separated list of ID's like this:
12,15,33
Now I need to create a UDF that accepts this string of ID's and returns the corresponding Category Names for each, like this:
'Sports,Education,Science'
These are the 3 category names for the ID's of 12, 15 and 33.
Ideally, using the input param of the UDF like this would be perfect:
select @list = @list + ', ' + convert(varchar(100), AOC.CategoryName)
from AccountOpeningCategories AOC
where AOC.AccountOpeningCategoryID in (@CategoryIDList)
But that doesn't work. I can't use temp tables in a UDF nor can I split the ID string in to a table to use as part of my select. Also, I can't execute a sproc in a select from another sproc.
Since I've learned many ways how NOT to do this, I was hoping someone could look at my sproc below (which works) and tell me how I can convert this to a UDF to call within a select of another sproc like this:
select F.column1, F.column2, dbo.acct_f_ReturnCategoryList(F.CategoryIDList) as CategoryList
from Foo as F
Thanks,
Mark
alter PROCEDURE acct_f_ReturnCategoryList
@IDListvarchar(100)
as
Declare
@listvarchar(8000),
@sqlvarchar(8000),
@UIDuniqueidentifier,
@idxsmallint,
@Delimitervarchar(5),
@slicevarchar(100)
-------------------------------------------------------------------------------
-- Split the incoming comma separated list in to a temp table and match it with
-- a guid in case multiple users are in the DB at the same time.
-------------------------------------------------------------------------------
set @UID = newid()
select @idx = 1
set @Delimiter = ','
if len(@IDList)<1 or @IDList is null return
while @idx!= 0
begin
set @idx = charindex(@Delimiter,@IDList)
if @idx!=0
set @slice = left(@IDList,@idx - 1)
else
set @slice = @IDList
if(len(@slice)>0)
insert into dbo.tempIDListTable(TempID, GUID) values(@slice, @UID)
set @IDList = right(@IDList,len(@IDList) - @idx)
if len(@IDList) = 0 break
end
-------------------------------------------------------------------------------
set @list = ''
select @list = @list + ', ' + convert(varchar(100), AOC.CategoryName)
from AccountOpeningCategories AOC, dbo.tempIDListTable IDLT
where AOC.AccountOpeningCategoryID = IDLT.TempID
and IDLT.[GUID] = @UID
-- Clear this users entry.
delete from tempIDListTable where GUID = @UID
-- Return the list.
select @list
June 27, 2013 at 2:08 pm
I have 2 links for you that will improve the performance and give you the solution for this.
Avoid using UDF unless they're iTVF (inLine Table-Valued Functions).
Tally OH! An Improved SQL 8K “CSV Splitter” Function[/url]
Creating a comma-separated list (SQL Spackle)[/url]
For help with code, please post DDL and consumable data to help us in the development. Check the article linked in my signature for more information on this.
June 28, 2013 at 5:23 am
Luis,
Exactly what I was looking for. Thanks!
Mark
June 28, 2013 at 7:42 am
And if at all possible you should consider splitting these attributes into their own rows. Storing multiple values in a single field violates first normal form.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply