?
Solved

Inline table function error

Posted on 2009-05-01
3
Medium Priority
?
248 Views
Last Modified: 2012-05-06
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

0
Comment
Question by:crompnk
3 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24280638
Hello crompnk,

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


Regards,

Aneesh
0
 

Author Comment

by:crompnk
ID: 24281734
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
 
LVL 31

Accepted Solution

by:
RiteshShah earned 750 total points
ID: 24284544
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question