Board index » delphi » User-Defined Function on M$ SQL server

User-Defined Function on M$ SQL server

I wrote function on SQL server:

CREATE FUNCTION dbo.fnTransform
(
 @sText  VARCHAR(1000)

)
RETURNS VARCHAR(1000)

AS
BEGIN

        DECLARE @sPomText VarChar(1000)
        /*Sets variables to initial values*/
        SET @sPomText=''

        IF BasicTable.Message= 'Try'
               SET  @sPomText = dbo.fnTransform11(@sText)
        ELSE
                SET  @sPomText = dbo.fnTransform22(@sText)

        RETURN (@sPomText)
END

but I get mistake Error 107: The column prefix BasicTable does not match
with a table name or alias name used in query.

BasicTable is table on server and Message is field in that table. Where is
mistake?

 

Re:User-Defined Function on M$ SQL server


Hi Johny,

I don't think you can address fields directly like that.  You might try
integrating something like this:

declare @Msg VarChar(1000)

set @Msg = (select top 1 Message from BasicTable) -- or whatever

if @Msg = 'Try'  -- etc.

HTH,

John

Quote
"Johny" <goky2...@beograd.com> wrote in message

news:3e42d361@newsgroups.borland.com...
Quote
> I wrote function on SQL server:

> CREATE FUNCTION dbo.fnTransform
> (
>  @sText  VARCHAR(1000)

> )
> RETURNS VARCHAR(1000)

> AS
> BEGIN

>         DECLARE @sPomText VarChar(1000)
>         /*Sets variables to initial values*/
>         SET @sPomText=''

>         IF BasicTable.Message= 'Try'
>                SET  @sPomText = dbo.fnTransform11(@sText)
>         ELSE
>                 SET  @sPomText = dbo.fnTransform22(@sText)

>         RETURN (@sPomText)
> END

> but I get mistake Error 107: The column prefix BasicTable does not match
> with a table name or alias name used in query.

> BasicTable is table on server and Message is field in that table. Where is
> mistake?

Re:User-Defined Function on M$ SQL server


You need to select the value of message in a variable then test the contens

ps: BasicTable has only one row??  Where are the "where" clause??

-- cut here --     :))

declare
 @strTemp

select
 @strTemp = Message
from
 BasicTable
where
 -- where clause

IF @strTemp = 'Try'
 SET  @sPomText = dbo.fnTransform11(@sText)
ELSE
 SET  @sPomText = dbo.fnTransform22(@sText)

Carlos Longen
car...@icalsc.com.br
Microcel Solu??es Corporativas

"Johny" <goky2...@beograd.com> escreveu na mensagem
news:3e42d361@newsgroups.borland.com...

Quote
> I wrote function on SQL server:

> CREATE FUNCTION dbo.fnTransform
> (
>  @sText  VARCHAR(1000)

> )
> RETURNS VARCHAR(1000)

> AS
> BEGIN

>         DECLARE @sPomText VarChar(1000)
>         /*Sets variables to initial values*/
>         SET @sPomText=''

>         IF BasicTable.Message= 'Try'
>                SET  @sPomText = dbo.fnTransform11(@sText)
>         ELSE
>                 SET  @sPomText = dbo.fnTransform22(@sText)

>         RETURN (@sPomText)
> END

> but I get mistake Error 107: The column prefix BasicTable does not match
> with a table name or alias name used in query.

> BasicTable is table on server and Message is field in that table. Where is
> mistake?

Re:User-Defined Function on M$ SQL server


Thank you. You helped me very much.

Quote
"Carlos Longen" <car...@icalsc.com.br> wrote in message

news:3e430178$1@newsgroups.borland.com...
Quote
> You need to select the value of message in a variable then test the
contens

> ps: BasicTable has only one row??  Where are the "where" clause??

> -- cut here --     :))

> declare
>  @strTemp

> select
>  @strTemp = Message
> from
>  BasicTable
> where
>  -- where clause

> IF @strTemp = 'Try'
>  SET  @sPomText = dbo.fnTransform11(@sText)
> ELSE
>  SET  @sPomText = dbo.fnTransform22(@sText)

> Carlos Longen
> car...@icalsc.com.br
> Microcel Solu??es Corporativas

> "Johny" escreveu na mensagem
> > I wrote function on SQL server:

> > CREATE FUNCTION dbo.fnTransform
> > (
> >  @sText  VARCHAR(1000)

> > )
> > RETURNS VARCHAR(1000)

> > AS
> > BEGIN

> >         DECLARE @sPomText VarChar(1000)
> >         /*Sets variables to initial values*/
> >         SET @sPomText=''

> >         IF BasicTable.Message= 'Try'
> >                SET  @sPomText = dbo.fnTransform11(@sText)
> >         ELSE
> >                 SET  @sPomText = dbo.fnTransform22(@sText)

> >         RETURN (@sPomText)
> > END

> > but I get mistake Error 107: The column prefix BasicTable does not match
> > with a table name or alias name used in query.

> > BasicTable is table on server and Message is field in that table. Where
is
> > mistake?

Other Threads