Temporary Table function does not work with Strings

can you show us how you call the function, ie with which value?

I get the error when I do this:

select * from vwAllocHistPYR
where plah_trn_no IN
(Select cast(value as nchar(8)) from dbo.parmstolist('4i016447, 4i016448')))


It works when I do this:

select * from vwAllocHistPYR
where plah_btch_no IN
(Select cast(value as int) from dbo.parmstolist('1320,1321'))

does this query work alone for you (it does on my test server):
Select cast(value as nchar(8)) from dbo.parmstolist('4i016447, 4i016448')

Yes - it does work

the the problem is outside that part of code, also I don't see the word COLLATE at all in the code you posted, but it is mentionned in the error message.
please post more about this...

This is my Select statement in it's entirety.  I tried adding a collate statement but it it didn't work (I'm not sure how/where to add it).

select * from vwAllocHistPYR
where plah_trn_no IN
(Select cast(value as nchar(8)) from dbo.parmstolist('4i016447, 4i016448'))

Pls advise

auke t...that did the trick.  angelIII...yours looks as though it would too.  I've now taken it to the true final state I want to get to and I have another related problem...points increased accordingly.

Instead of passing a string to dbo.parmstolist as a parameter within an SP I need to call a function within dbo.parmstolist.....so a function within a function.

This bit works perfectly:

select * from vwAllocHistPYR
where plah_pay_no collate database_default IN
(Select cast(value as nchar(8)) collate database_default
from dbo.parmstolist('4P009706, 4P009863'))

However, the string being paased to parmstolist is dynamic.  So I wrote this function:

ALTER  Function GetPayTranListPYR(@TransactionID nvarchar(8))
Returns nvarchar(1000)
AS
begin
declare @MyResults nvarchar(1000)
begin
set @myresults = ''
Select @myresults = @Myresults + ltrim(rtrim(plah_pay_no)) + ', '
From vwallochistpyr
Where plah_trn_no = @TransactionID
and plah_pay_no <> ''
return left(@MyResults,len(@MyResults)-1)
end
end

When I call the above function with this: select dbo.getpaytranlistpyr('4i016447'), I get this result:
4P009706, 4P009863

So, I though that I could embed the above function within my initial select statement like this:

select * from vwAllocHistPYR
where plah_pay_no collate database_default IN
(Select cast(value as nchar(8)) collate database_default
from dbo.parmstolist(select dbo.getpaytranlistpyr('4i016447')))

Unfortunately, I get this error message:
Server: Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'select'.
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near ')'.

Can anyone help?

I've found a way around it.  I altered the original function that angeIII provided in another post (Q_21627393) so that it calls my function GetPayTranListPYR.  The revised function looks like this:

create     FUNCTION dbo.ParmsToListExtraFunction (@TransactionID nchar(8))
returns @result TABLE (Value varchar(30))
AS  
begin
     DECLARE @TempList table
          (
          Value varchar(30)
          )

     DECLARE @Value varchar(30), @Pos int, @Parameters varchar(500)
      set @Parameters = dbo.getpaytranlistpyr(@TransactionID)
     SET @Parameters = LTRIM(RTRIM(@Parameters))+ ','
     SET @Pos = CHARINDEX(',', @Parameters, 1)

     IF REPLACE(@Parameters, ',', '') <> ''
     BEGIN
          WHILE @Pos > 0
          BEGIN
               SET @Value = LTRIM(RTRIM(LEFT(@Parameters, @Pos - 1)))
               IF @Value <> ''
               BEGIN
                    INSERT INTO @TempList (Value) VALUES (@Value) --Use Appropriate conversion
               END
               SET @Parameters = RIGHT(@Parameters, LEN(@Parameters) - @Pos)
               SET @Pos = CHARINDEX(',', @Parameters, 1)

          END
     END    
     INSERT @result
     SELECT value
        FROM @TempList
     RETURN
END