'declare' syntax in a UDF

[Not Me]

Hi, I'm trying to create a function that returns a table, however I want
to use a local variable in there and enterprise manager ain't liking it!

The error I get is number 156 'incorrect syntax near the keyword
'declare'.. hopefully this is just a simple thing where I've put it in
the wrong place.

The code follows:

CREATE FUNCTION AFGroupedTotals (@campaign nvarchar(30),@d atefrom
smalldatetime, @dateto smalldatetime, @prospect nvarchar(30), @type
nvarchar(20))

RETURNS TABLE AS
RETURN

declare @set nvarchar(150)

select "Total Pledged" as info, sum(total) as tot
FROM AFresponseTotal s (@campaign, @datefrom, @dateto,@prospe ct)
Cheers for any help,
Chris

[Simon Hayes]

"Not Me" <No***********@ here.com> wrote in message
news:ck******** **@ucsnew1.ncl. ac.uk...

Hi, I'm trying to create a function that returns a table, however I want
to use a local variable in there and enterprise manager ain't liking it!

The error I get is number 156 'incorrect syntax near the keyword
'declare'.. hopefully this is just a simple thing where I've put it in the
wrong place.

The code follows:

CREATE FUNCTION AFGroupedTotals (@campaign nvarchar(30),@d atefrom
smalldatetime, @dateto smalldatetime, @prospect nvarchar(30), @type
nvarchar(20))

RETURNS TABLE AS
RETURN

declare @set nvarchar(150)

select "Total Pledged" as info, sum(total) as tot
FROM AFresponseTotal s (@campaign, @datefrom, @dateto,@prospe ct)
Cheers for any help,
Chris

You seem to be mixing inline and multi-statement syntax. If you just say
RETURN TABLE, then the rest of the function can only be a single SELECT
statement; if you want to use multiple statements in the function, then you
must define the structure of the table you're returning. See the examples in
Books Online under CREATE FUNCTION.

In your function, you haven't defined the structure of the returned table,
so the only thing you can have in the body of the function is a single
SELECT.

Simon

[Not Me]

Simon Hayes wrote:

"Not Me" <No***********@ here.com> wrote in message
news:ck******** **@ucsnew1.ncl. ac.uk...

The error I get is number 156 'incorrect syntax near the keyword
'declare'.. hopefully this is just a simple thing where I've put it in the
wrong place.

The code follows:

CREATE FUNCTION AFGroupedTotals (@campaign nvarchar(30),@d atefrom
smalldatetime , @dateto smalldatetime, @prospect nvarchar(30), @type
nvarchar(20 ))
RETURNS TABLE AS
RETURN
declare @set nvarchar(150)
select "Total Pledged" as info, sum(total) as tot
FROM AFresponseTotal s (@campaign, @datefrom, @dateto,@prospe ct)

You seem to be mixing inline and multi-statement syntax. If you just say
RETURN TABLE, then the rest of the function can only be a single SELECT
statement; if you want to use multiple statements in the function, then you
must define the structure of the table you're returning. See the examples in
Books Online under CREATE FUNCTION.

Aha! sounds about right, just needed a little shunt in the right
direction.. gonna have nightmares about BOL :p

cheers,
Chris