Sql 2k UDF help

14 Jul 2006 1:07 PM

Fabri

I don't understand why in this UDF:

=======================================================================
create function dbo.MyUDF(@dove varchar(100))
returns varchar(8000)
as
begin

    declare @out varchar(8000)
    set @out = ''

    return
    (
    select @out = @out + ';' + TC.name
    from
        TC
    join
        TL                on tc.field = tl.field
    where
        tc.field = @dove
    )

    set @out= substring (@out, 2, 8000)
    set @out = replace(@out, ';', char(13)+char(10))

    return @out

end
=======================================================================

I get error when try to create this from QA:

----->    Sintax Error near " = " (is the = near @out).

I tried this solution out of an UDF and it worked perfectly.

Perhaps I cannot use it in UDF?

Thx

--
fabri
MKDS: Joker® - <waiting for ds lite...>
AC:Fabrizio, Kanoemi, 1890 4700 1546
Tetris DS: 748422 559210

14 Jul 2006 1:13 PM

Tracy McKibben

Fabri wrote:
Show quote

> I don't understand why in this UDF:
>
> =======================================================================
> create function dbo.MyUDF(@dove varchar(100))
> returns varchar(8000)
> as
> begin
>
>     declare @out varchar(8000)
>     set @out = ''
>
>     return
>     (
>     select @out = @out + ';' + TC.name
>     from
>         TC
>     join
>         TL                on tc.field = tl.field
>     where
>         tc.field = @dove
>     )
>
>
>     set @out= substring (@out, 2, 8000)
>     set @out = replace(@out, ';', char(13)+char(10))
>
>     return @out
>
> end
> =======================================================================
>
>
> I get error when try to create this from QA:
>
>
> ----->    Sintax Error near " = " (is the = near @out).
>
> I tried this solution out of an UDF and it worked perfectly.
>
> Perhaps I cannot use it in UDF?
>
> Thx
>
>

Why do you have two return statements?

--
Tracy McKibben
MCDBA
http://www.realsqlguy.com

14 Jul 2006 1:15 PM

Anith Sen

>> I tried this solution out of an UDF and it worked perfectly.

Your SELECT statement has an undocumented syntax, that occasionally breaks
and hence it is better to avoid such methods. Several instances of failures
have been documented and you can find some if you search the archives of
this newsgroup.

--
Anith