pass varialble to table function insert

[747576]

Hi,

This question relates to question: thread183-1545958

I have a dynamis sql statement that returns a view of results. I'd like to use the same statement in a table function, which I can pass the variable @type into.

When I constructed the function I recieved the error :
"An INSERT statement cannot contain a SELECT statement that assigns values to a variable."

Is it possible to pass a variable to the script below when in a function or procedure.

Thank you

Declare @type VarChar(30)
Declare @SQL VarChar(MAX)

set @type = 'type1'

Select @SQL = '

SELECT
	A.ID'

Select @SQL = @SQL + ', min(CASE when [A].[NAME] = '''+@type+''' and [A].[IDTYPE] = ''FIRST'' then [A].[VALUE] ELSE NULL END) as ['+ @type +'_FIRST]'
Select @SQL = @SQL + ', min(CASE when [A].[NAME] = '''+@type+''' and [A].[IDTYPE] = ''SECOND'' then [A].[VALUE] ELSE NULL END) as ['+ @type +'_SECOND]'
Select @SQL = @SQL + ', min(CASE when [A].[NAME] = '''+@type+''' and [A].[IDTYPE] = ''THIRD'' then [A].[VALUE] ELSE NULL END) as ['+ @type +'_THIRD]'

Select @SQL = @SQL + '
FROM (
SELECT 
	LEFT(S.ID,LEN(S.ID)-1) AS ID
	, S.IDTYPE
	, CS.[NAME]
	, CS.[VALUE] 
	FROM HEADER S LEFT JOIN RESULT CS 
		ON S.ID = CS.ID
WHERE CS.[NAME] IN('''+@type+''') 
)A
GROUP BY A.ID

'

exec ( @SQL)