declare syntax in a UDF

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),@.datefrom
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 AFresponseTotals (@.campaign, @.datefrom, @.dateto,@.prospect)

Cheers for any help,
Chris"Not Me" <Noone.is.home@.here.com> wrote in message
news:ckoccr$olo$1@.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),@.datefrom
> 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 AFresponseTotals (@.campaign, @.datefrom, @.dateto,@.prospect)
>
> 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|||Simon Hayes wrote:
> "Not Me" <Noone.is.home@.here.com> wrote in message
> news:ckoccr$olo$1@.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),@.datefrom
>>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 AFresponseTotals (@.campaign, @.datefrom, @.dateto,@.prospect)
> 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