Function won't return varchar variable - T-SQL

Transact-SQL
1

Hi all!
I'm new to SQL but I have a function that isn't returning a value like it should and I'm hoping that someone can help. The code:

CREATE OR ALTER FUNCTION [ccdd].[fn_DemographicsLookup](@input VARCHAR(100), @colName VARCHAR(100))
RETURNS VARCHAR(1000)
AS
BEGIN
  DECLARE @str NVARCHAR(1000)
  DECLARE @tab TABLE (item VARCHAR(50))
  DECLARE @rts NVARCHAR(1000)
  DECLARE @xml XML
  DECLARE @num INT

  SET @str = ''
  set @rts = ''
  IF @input = ''
  BEGIN
	RETURN NULL
  END
  SET @xml = N'<t>' + REPLACE(@input,'&','</t><t>') + '</t>'
  INSERT INTO @tab 
  SELECT r.value('.','int') as item
  FROM  @xml.nodes('/t') as records(r)

  SELECT @str = (CONCAT( COALESCE(@str,''),STUFF((SELECT ' ' + item + ','
				FROM @tab
				FOR XML PATH('')) ,1,1,'')))

  SELECT @str = TRIM(',' FROM @str)
  SELECT @rts =  @rts + CONCAT(COALESCE(@colName,''),'|') FROM prod_test.ccdd.DemographicsLookup dl WHERE dl.id IN (SELECT * FROM STRING_SPLIT(@str,','))
  SET @rts = TRIM( '|' FROM @rts)

  RETURN @rts
END

When I execute the function

exec ccdd.fn_DemographicsLookup @input = '2&3', @colName = 'nutrition'

I get the following:

(2 rows affected)

Completion time: 2022-06-29T19:25:26.9266291-04:00

However when I run the code outside the function like so:

  DECLARE @str VARCHAR(1000)
  DECLARE @tab TABLE (item varchar(50))
  DECLARE @rts VARCHAR(1000)
  DECLARE @xml XML
  DECLARE @num INT

  SET @str = ''
  set @rts = ''
 -- IF @input = ''
 -- BEGIN
	--RETURN NULL
 -- END
  SET @xml = N'<t>' + REPLACE('2&3&5','&','</t><t>') + '</t>'
  INSERT INTO @tab 
  SELECT r.value('.','int') as item
  FROM  @xml.nodes('/t') as records(r)

  SELECT @str = (CONCAT( COALESCE(@str,''),STUFF((SELECT ' ' + item + ','
            FROM @tab
            FOR XML PATH('')) ,1,1,'')))

  SELECT @str = TRIM(',' FROM @str)
  SELECT @rts =  @rts + CONCAT(COALESCE(nutrition,''),'|') FROM prod_test.ccdd.DemographicsLookup dl WHERE dl.id IN (select * from string_split(@str,','))
  SELECT @rts = TRIM( '|' FROM @rts)
  SELECT @rts

I get the correct result:

(No column name)
Requires modified consistency diet for foods|Requires modified consistency diet for fluids|Requires high calorie diet

Could someone please help?? Thanks in advance

2

Welcome,

What consumes the result of this function? You will hit some issues down the line with this function. Are you interested in changing things? Does the process that consumes this data expect it to be pipe | delimited?

with @rts being 1000, if one sends 2&3&4&5&6, this will truncate things.

3

Hi yosiasz!.
This is an inline function which is used to return the string for the column values represented by the numbers and ampersands. No further process is needed as the call to it is as so:

ccdd.fn_DemographicsLookup(COALESCE(individualParticipation,''),'individualParticipation')individualParticipation,

I'm totally unsure what you mean by the truncation, please explain further?

4 
DECLARE @str VARCHAR(1000)
DECLARE @tab TABLE (item varchar(50))
DECLARE @rts VARCHAR(1000)
DECLARE @xml XML
DECLARE @num INT


declare @DemographicsLookup table(id int identity(1,1), nutrition varchar(max) )
insert into @DemographicsLookup
select '1=Requires modified consistency diet for foods.2=Requires modified consistency diet for foods.3=Requires modified consistency diet for foods1=Requires modified consistency diet for foods.2=Requires modified consistency diet for foods.3=Requires modified consistency diet for food1=Requires modified consistency diet for foods.2=Requires modified consistency diet for foods.3=Requires modified consistency diet for food1=Requires modified consistency diet for foods.2=Requires modified consistency diet for foods.3=Requires modified consistency diet for food1=Requires modified consistency diet for foods.2=Requires modified consistency diet for foods.3=Requires modified consistency diet for food1=Requires modified consistency diet for foods.2=Requires modified consistency diet for foods.3=Requires modified consistency diet for food' union
select '1=Requires modified consistency diet for fluids.2=Requires modified consistency diet for fluids.3=Requires modified consistency diet for fluids1=Requires modified consistency diet for foods.2=Requires modified consistency diet for foods.3=Requires modified consistency diet for food1=Requires modified consistency diet for foods.2=Requires modified consistency diet for foods.3=Requires modified consistency diet for food1=Requires modified consistency diet for foods.2=Requires modified consistency diet for foods.3=Requires modified consistency diet for food1=Requires modified consistency diet for foods.2=Requires modified consistency diet for foods.3=Requires modified consistency diet for food1=Requires modified consistency diet for foods.2=Requires modified consistency diet for foods.3=Requires modified consistency diet for food' union
select 'Requires high calorie diet1=Requires modified consistency diet for foods.2=Requires modified consistency diet for foods.3=Requires modified consistency diet for food1=Requires modified consistency diet for foods.2=Requires modified consistency diet for foods.3=Requires modified consistency diet for food1=Requires modified consistency diet for foods.2=Requires modified consistency diet for foods.3=Requires modified consistency diet for food1=Requires modified consistency diet for foods.2=Requires modified consistency diet for foods.3=Requires modified consistency diet for food1=Requires modified consistency diet for foods.2=Requires modified consistency diet for foods.3=Requires modified consistency diet for food1=Requires modified consistency diet for foods.2=Requires modified consistency diet for foods.3=Requires modified consistency diet for food1=Requires modified consistency diet for foods.2=Requires modified consistency diet for foods.3=Requires modified consistency diet for food1=Requires modified consistency diet for foods.2=Requires modified consistency diet for foods.3=Requires modified consistency diet for food1=Requires modified consistency diet for foods.2=Requires modified consistency diet for foods.3=Requires modified consistency diet for food1=Requires modified consistency diet for foods.2=Requires modified consistency diet for foods.3=Requires modified consistency diet for food1=Requires modified consistency diet for foods.2=Requires modified consistency diet for foods.3=Requires modified consistency diet for food1=Requires modified consistency diet for foods.2=Requires modified consistency diet for foods.3=Requires modified consistency diet for food1=Requires modified consistency diet for foods.2=Requires modified consistency diet for foods.3=Requires modified consistency diet for food1=Requires modified consistency diet for foods.2=Requires modified consistency diet for foods.3=Requires modified consistency diet for food1=Requires modified consistency diet for foods.2=Requires modified consistency diet for foods.3=Requires modified consistency diet for food1=Requires modified consistency diet for foods.2=Requires modified consistency diet for foods.3=Requires modified consistency diet for food1=Requires modified consistency diet for foods.2=Requires modified consistency diet for foods.3=Requires modified consistency diet for food1=Requires modified consistency diet for foods.2=Requires modified consistency diet for foods.3=Requires modified consistency diet for food1=Requires modified consistency diet for foods.2=Requires modified consistency diet for foods.3=Requires modified consistency diet for food1=Requires modified consistency diet for foods.2=Requires modified consistency diet for foods.3=Requires modified consistency diet for food1=Requires modified consistency diet for foods.2=Requires modified consistency diet for foods.3=Requires modified consistency diet for food1=Requires modified consistency diet for foods.2=Requires modified consistency diet for foods.3=Requires modified consistency diet for food1=Requires modified consistency diet for foods.2=Requires modified consistency diet for foods.3=Requires modified consistency diet for food1=Requires modified consistency diet for foods.2=Requires modified consistency diet for foods.3=Requires modified consistency diet for food1=Requires modified consistency diet for foods.2=Requires modified consistency diet for foods.3=Requires modified consistency diet for food1=Requires modified consistency diet for foods.2=Requires modified consistency diet for foods.3=Requires modified consistency diet for food1=Requires modified consistency diet for foods.2=Requires modified consistency diet for foods.3=Requires modified consistency diet for food1=Requires modified consistency diet for foods.2=Requires modified consistency diet for foods.3=Requires modified consistency diet for food1=Requires modified consistency diet for foods.2=Requires modified consistency diet for foods.3=Requires modified consistency diet for food1=Requires modified consistency diet for foods.2=Requires modified consistency diet for foods.3=Requires modified consistency diet for food1=Requires modified consistency diet for foods.2=Requires modified consistency diet for foods.3=Requires modified consistency diet for food1=Requires modified consistency diet for foods.2=Requires modified consistency diet for foods.3=Requires modified consistency diet for food1=Requires modified consistency diet for foods.2=Requires modified consistency diet for foods.3=Requires modified consistency diet for food1=Requires modified consistency diet for foods.2=Requires modified consistency diet for foods.3=Requires modified consistency diet for food1=Requires modified consistency diet for foods.2=Requires modified consistency diet for foods.3=Requires modified consistency diet for food.JUMBO SANA'

SET @str = ''
set @rts = ''
-- IF @input = ''
-- BEGIN
--RETURN NULL
-- END
SET @xml = N'<t>' + REPLACE('2&3&5','&','</t><t>') + '</t>'
INSERT INTO @tab 
SELECT r.value('.','int') as item
FROM  @xml.nodes('/t') as records(r)

SELECT @str = (CONCAT( COALESCE(@str,''),STUFF((SELECT ' ' + item + ','
        FROM @tab
        FOR XML PATH('')) ,1,1,'')))

SELECT @str = TRIM(',' FROM @str)
SELECT @rts =  @rts + CONCAT(COALESCE(nutrition,''),'|') FROM @DemographicsLookup dl WHERE dl.id IN (select * from string_split(@str,','))
SELECT @rts = TRIM( '|' FROM @rts)
SELECT @rts

Notice final result, it is truncated. JUMBO SANA is not there. I would not recommend pipe delimited at all.

5

What I would recommend is the following which will return the data as json. Using @JeffModen DelimitedSplit8K function.

declare @input VARCHAR(100) = '2&3&4', @colName VARCHAR(100) = 'nutrition', @str nvarchar(1000) = '', @rts nvarchar(1000) = ''

declare @DemographicsLookup table(id int identity(1,1), nutrition varchar(2500) )
insert into @DemographicsLookup
select 'Requires modified consistency diet for foods' union
select 'Requires modified consistency diet for fluids' union
select 'Requires high calorie diet'
SELECT @str = TRIM(',' FROM @str)

;with src
as
(
select @input as input
)

 SELECT id, nutrition
 --@rts =  @rts + CONCAT(COALESCE(dl.nutrition,''),'|')
 from src 
 cross apply DelimitedSplit8K(src.input, '&')  s
 join @DemographicsLookup dl on dl.id = s.ItemNumber
 for json path

result

[
	{
		"id": 1,
		"nutrition": "Requires high calorie diet"
	},
	{
		"id": 2,
		"nutrition": "Requires modified consistency diet for fluids"
	},
	{
		"id": 3,
		"nutrition": "Requires modified consistency diet for foods"
	}
]
6

Thanks, unfortunately pipe delimited is a requirement... So how do I fix this?

7

Change the requirements. :wink: You still have not told us what consumes this data. Is it UI or some baked application, a reporting system, AS400? I don't think you can "fix" this. Even if you had it at varchar(max), it could work, it might not work.