UDF returning inline table - can't use variables ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

UDF returning inline table - can't use variables ?

Author

Topic

sqlghost
Starting Member

23 Posts

Posted - 2005-11-17 : 12:08:37

I'm trying to create a UDF that returns a table (off a single select statement). It's a very complex select clause and I'm trying to use variables but I'm not getting anywhere. I notice I can use variables no problem when returning a scalar from a UDF.

Is it possible to use variables (not parameters) inside a UDF that returns a table variable? I've found no examples online or in documentation where any variables other than parameters are used.

Thanks.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2005-11-17 : 13:27:06

You can use variables and parameters. So there is some other issue with your code.
Post your code if you want help debugging it.

Kristen
Test

22859 Posts

Posted - 2005-11-17 : 13:51:50

Here's some (somewhat edited!) code of mine in case it helps as an example:


--
PRINT 'Create function MyFunction'
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[MyFunction]') AND xtype IN (N'FN', N'IF', N'TF'))
	DROP FUNCTION dbo.MyFunction
GO

CREATE FUNCTION dbo.MyFunction
(
	@MyParam1	int,
	@MyParam2	int
)
RETURNS @X TABLE
(
	Col1	int NOT NULL,
	Col2	int NULL,
...
	PRIMARY KEY CLUSTERED 
	(
		Col1
	)
)
/* WITH ENCRYPTION */
AS
/*
 * MyFunction	DumDeDumDeDumDum
 *
 * Returns:
 *
 *	Lorem Ipsum
 */
BEGIN
DECLARE	@Local1	varchar(10),
	@Local2	varchar(2)

	SELECT	@Local1 = dbo.MyGetStuffFunction(@MyParam1, 'XXX'),
		@Local2 = dbo.MyGetStuffFunction(@MyParam1, 'YYY')

	INSERT @X
	SELECT	Col1, 
		Col2,
		...
	FROM	dbo.MyTable
	WHERE	    MyPK_ID = @MyParam2
		AND MyColX = @Local1
		AND MyColY = @Local2

	RETURN
END
--================== MyFunction ==================--
GO
PRINT 'Create function MyFunction - DONE'
GO

Kristen

chloraphil
Starting Member

7 Posts

Posted - 2005-11-17 : 13:52:54

This is not my 'production' query, but I've reproduced behavior here:

CREATE FUNCTION dbo.PatientDaysPerMonth (@startDate smalldatetime)

RETURNS TABLE AS

RETURN (
declare @year int
set @year = Year(@startdate)
select * from patients where year(admit_date) = @year
)

I have tried declaring and/or setting the variable in various places.

chloraphil
Starting Member

7 Posts

Posted - 2005-11-17 : 13:54:34

Hmm I wonder if that means I'll need to actually define my table variable schema just to use variables...

by the way sqlghost was the bugmenot handle. I felt guilty so I signed up for real.

Kristen
Test

22859 Posts

Posted - 2005-11-17 : 14:06:14

Will this work?


CREATE FUNCTION dbo.PatientDaysPerMonth (@startDate smalldatetime)

RETURNS TABLE AS 
BEGIN
declare @year int
set @year = Year(@startdate)

RETURN (
	select * from patients where year(admit_date) = @year
)
END

Kristen

chloraphil
Starting Member

7 Posts

Posted - 2005-11-17 : 14:11:02

Negatory. I tried it out various ways and did my time Googling, etc, I think I'll have to do it the 'hard' way.

Kristen
Test

22859 Posts

Posted - 2005-11-17 : 14:17:19

"I think I'll have to do it the 'hard' way"

Hehehehe ... sorry about that!

Kristen

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-11-17 : 14:52:07

You cannot declare local variables in an inline table function.

You can declare parameters in a multi-statement table-valued function.

This is all clearly documented in the CREATE FUNCTION topic in SQL Server Books Online.

CODO ERGO SUM

chloraphil
Starting Member

7 Posts

Posted - 2005-11-17 : 15:04:36

MVJ: I reviewed Books Online thoroughly before posting. I saw where it says assignment statements are allowed in the body of a multi-statement function, but I still do not see where it says declaration of local variables is not allowed in an inline table function. It's possible that I missed it, but saying x is allowed for y does not necessarily mean x is NOT allowed for z. Hence my post. :)

Kristen: Thanks for your helpful comments. It's working now.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2005-11-17 : 15:07:41

But you can declare variables in a multi-statement table function. So just change your function from in-line to non-in-line.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-11-17 : 15:26:16

BOL says this:
"Inline functions are table-valued functions defined with a single SELECT statement making up the body of the function."

quote:
Originally posted by chloraphil

MVJ: I reviewed Books Online thoroughly before posting. I saw where it says assignment statements are allowed in the body of a multi-statement function, but I still do not see where it says declaration of local variables is not allowed in an inline table function. It's possible that I missed it, but saying x is allowed for y does not necessarily mean x is NOT allowed for z. Hence my post. :)

Kristen: Thanks for your helpful comments. It's working now.

CODO ERGO SUM

chloraphil
Starting Member

7 Posts

Posted - 2005-11-17 : 15:36:45

Aha! There it is, on line 138! Yep, it all makes perfect sense in hindsight.

:) Thanks all, I've got it now.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources