Using variables in functions that return table

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 2005 Forums

Transact-SQL (2005)

Using variables in functions that return table

Author

Topic

jorgen
Starting Member

3 Posts

Posted - 2007-10-29 : 07:59:33

Hi everyone.

I have a UDF that returns table, but I need some internal variables for that function. So, I tried something like:



CREATE FUNCTION f_GetOffer (@startDate datetime)
RETURNS table

AS
declare @seasonDate datetime
set @seasonDate=(select CalcSeasonDate from Seasons where sid=19)

RETURN
(
SELECT * from Offers where StartDate=@startDate
)

..but I get standard "Incorrect syntax near the keyword 'declare' " error message.

How do I declare @seasonDate variable ? I also tried enclosing the function code below AS in begin..end , but then I get "A RETURN statement with a return value cannot be used in this context" .

What am I doing wrong ?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-10-29 : 08:12:08

The thing is that you are trying to create inline table valued function where only allowed statement is RETURN. for e.g.

CREATE FUNCTION f_GetOffer (@startDate datetime)
RETURNS table
as
Return
(
SELECT * from Offers where StartDate=@startDate
)

If you want to create multi-statement function, you need to define table structure as well in the RETURN clause:

CREATE FUNCTION f_GetOffer (@startDate datetime)
RETURNS @temp table
(
col1 int,
col2 varchar(20), ...
)
As
begin
declare @seasonDate datetime
set @seasonDate=(select CalcSeasonDate from Seasons where sid=19)

declare @temp1 table
(
col1 int,
col2 varchar(20), ...
)

insert into @temp1
SELECT * from Offers where StartDate=@startDate

RETURN
end

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources