Inline table function error

Hi

I have a dynamis sql statement that returns a view of results. I'd like to use the same statement in a inline table function (so I can query later on), which I can pass the variable @typename 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
CREATE TABLE [HEADER](
	[ID] [varchar](30) NOT NULL,
	[IDTYPE] [varchar](10) NULL
)
GO
 
CREATE TABLE [RESULT](
	[ID] [varchar](30) NOT NULL,
	[NAME] [varchar](30) NOT NULL,
	[VALUE] float
)
GO
 
CREATE TABLE [TYPE](
	[NAME] [varchar](30) NOT NULL
)
GO
 
INSERT INTO [TYPE] VALUES('type1')
INSERT INTO [TYPE] VALUES('type2')
INSERT INTO [TYPE] VALUES('type3')
GO
 
INSERT INTO HEADER VALUES('1A','FIRST')
INSERT INTO HEADER VALUES('1B','SECOND')
INSERT INTO HEADER VALUES('1C','THIRD')
INSERT INTO HEADER VALUES('2A','FIRST')
INSERT INTO HEADER VALUES('2B','SECOND')
INSERT INTO HEADER VALUES('2C','THIRD')
INSERT INTO HEADER VALUES('3A','FIRST')
INSERT INTO HEADER VALUES('3B','SECOND')
INSERT INTO HEADER VALUES('3C','THIRD')
GO
 
INSERT INTO RESULT VALUES('1A','type1','0.01')
INSERT INTO RESULT VALUES('1A','type2','0.06')
INSERT INTO RESULT VALUES('1A','type3','0.04')
INSERT INTO RESULT VALUES('1B','type1','0.56')
INSERT INTO RESULT VALUES('1B','type2','0.06')
INSERT INTO RESULT VALUES('1B','type3','0.61')
INSERT INTO RESULT VALUES('1C','type1','0.01')
INSERT INTO RESULT VALUES('1C','type2','0.56')
INSERT INTO RESULT VALUES('1C','type3','0.01')
GO
 
 
 
CREATE FUNCTION [dbo].[TYPE_DATA] ( @typename varchar(30) )
 
returns @TempTable table
   (
    ID      varchar(20),
    [FIRST]       float,
    [SECOND]        float,
    [THIRD]      float    
   )
 
AS
 
BEGIN
 
 
Declare @type VarChar(30)
Declare @SQL VarChar(MAX)
 
set @type = @typename
 
insert @TempTable 
 
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 [FIRST]'
Select @SQL = @SQL + ', min(CASE when [A].[NAME] = '''+@type+''' and [A].[IDTYPE] = ''SECOND'' then [A].[VALUE] ELSE NULL END) as [SECOND]'
Select @SQL = @SQL + ', min(CASE when [A].[NAME] = '''+@type+''' and [A].[IDTYPE] = ''THIRD'' then [A].[VALUE] ELSE NULL END) as [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)
 
RETURN
 
END

Open in new window

crompnkData Management SpecialistAsked:
Who is Participating?
 
RiteshShahConnect With a Mentor Commented:
you can't use your SP in function for sure. if you want to join result of your SP with any other table, then one of the option is, return SP result into temp table, join it with anything you want and drop temp table.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Hello crompnk,

You cannot use DYNAMIC SQL inside a function, you have to rewrite it as a Procedure


Regards,

Aneesh
0
 
crompnkData Management SpecialistAuthor Commented:
Thanks for the help,

I have created a SP and it works good, how would I use this in a function to return a single value, please see code below.
CREATE PROCEDURE [dbo].[TYPE_DATA] ( @typename varchar(30) )
 
AS
 
BEGIN
 
 
Declare @type VarChar(30)
Declare @SQL VarChar(MAX)
 
set @type = @typename
 
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 [FIRST]'
Select @SQL = @SQL + ', min(CASE when [A].[NAME] = '''+@type+''' and [A].[IDTYPE] = ''SECOND'' then [A].[VALUE] ELSE NULL END) as [SECOND]'
Select @SQL = @SQL + ', min(CASE when [A].[NAME] = '''+@type+''' and [A].[IDTYPE] = ''THIRD'' then [A].[VALUE] ELSE NULL END) as [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)
 
RETURN
 
END
 
 
/***************************************************************************/
/***************************************************************************/
 
CREATE TABLE #TYPE_DATA (
    ID      varchar(20),
    [FIRST]       float,
    [SECOND]         float,
    [THIRD]      float 
    )
 
INSERT #TYPE_DATA
EXEC TYPE_DATA 'type1'
 
SELECT [FIRST]
FROM #TYPE_DATA WHERE ID = '2'

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.