[AccessD] OT: T-SQL 2005 - Building lookup rows from string...

Hi All,

Inspired by David response and using the following greate T-SQL dynamic programmic sources:

http://www.sommarskog.se/dynamic_sql.html#sp_executesqlong

http://www.sommarskog.se/arrays-in-sql-2005.html

I have got written a couple of UDF and SP to get lookup values wrapped into a string and "unwrapped back" to a table. The code of those UDF and SP follows. Please note that dynamic T-SQL has some security limitations. The code was tested with MS SQL 2005 Northwind sample database. Writing this code was an exercize to prepare to develop advanced XREF T-SQL quesries for a real customer. I hope you can find posted T-SQL code and URLs on dynamic SQL programming issue useful.

Thank you.

--
Shamil

P.S. Code (watch line wraps!):

--
-- -- SAMPLE CALL --
-- select * from udf_LookupListToTable('1,Beverages,2,Condiments,3,Confections,4,Dairy Products,5,Grains/Cereals,6,Meat/Poultry,7,Produce,8,Seafood')
--
CREATE FUNCTION udf_LookupListToTable (@list nvarchar(MAX))
   RETURNS @tbl TABLE (id int NOT NULL, [name] nvarchar(128)) AS

BEGIN
   DECLARE @pos        int,
           @nextpos    int,
           @valuelen   int

   SELECT @pos = 0, @nextpos = 1

   WHILE @nextpos > 0
   BEGIN
     
     DECLARE @index int,
             @id int,
             @name nvarchar(128)
     SET @index = 2
     WHILE @index > 0
     BEGIN 
      SELECT @nextpos = charindex(',', @list, @pos + 1)
      SELECT @valuelen = CASE WHEN @nextpos > 0
                              THEN @nextpos
                              ELSE len(@list) + 1
                         END - @pos - 1
      if @index = 2
        SET @id = convert(int, substring(@list, @pos + 1, @valuelen))
      if @index = 1
      begin
        SET @name = substring(@list, @pos + 1, @valuelen)
        INSERT @tbl (id, [name])
          VALUES (@id, @name)
      end
      SELECT @pos = @nextpos
      set @index = @index - 1
     END
   END
  RETURN
END
GO

--
-- -- SAMPLE CALL --
-- declare @resultList nvarchar(MAX)
-- EXEC SP_LookupTableToList 'Categories','CategoryId','CategoryName', 
--                            @resultList out
-- -- print @resultList
-- select * from udf_LookupListToTable(@resultList)
ALTER PROCEDURE [dbo].[SP_LookupTableToList] 
	  @tableName nvarchar(128),
	  @idFieldName nvarchar(128),
	  @valueFieldName nvarchar(128),
      @resultList nvarchar(4000) OUT
AS
BEGIN
	SET NOCOUNT ON;

    DECLARE @tbl TABLE (ResultList nvarchar(max))


	DECLARE @resultListColumnName nvarchar(128),
            
            @sql nvarchar(4000)

    set @resultListColumnName = 'ResultList'

	set @sql = 
	'
    SELECT 
	REPLACE(
	(
	  SELECT 
		CAST(@idFieldName as nvarchar(126)) + '','' + 
		@valueFieldName + '','' 
		FROM @tableName
		ORDER BY @valueFieldName
		FOR XML PATH('''')
	) + ''-'',
	'',-'','''')
	as @resultListColumnName'

	set @sql = REPLACE(@sql,'@tableName', at tableName)
	set @sql = REPLACE(@sql,'@idFieldName', at idFieldName)
	set @sql = REPLACE(@sql,'@valueFieldName', at valueFieldName)
	set @sql = REPLACE(@sql,'@resultListColumnName', at resultListColumnName)

    INSERT @tbl(ResultList) EXEC (@sql)

    SELECT @resultList = [ResultList] from @tbl

END
GO