Syntax error while calling database function

[Former Member]

Hi

Please apologize if I am asking stupid question

PowerBuilder 11.2 windows script

string as_depatment

select   f_department(21) into :as_depatment;

f_department is user defined function in sqlserver , I want to store the result in as_depatment. syntax error showing

Please help

Pol

[Former Member]

You should mark Neil's answer as correct so that your question will show as answered and he will get credit for correctly pointing out that the problem was a missing FROM clause.

[Former Member]

It is working fine after using below SELECT  dbo.f_department(21)  INTO as_dept FROM DUAL using sqlca; if sqlca.sqlcode= -1 then messagebox("Error",sqlca.sqlerrtext) end if Many Thanks

[Former Member]

You must be on Oracle then and not SQLServer if you are using the reserved word DUAL. 

[Former Member]

If you are using sql server, you should always schema-qualify your objects.  I believe this is a requirement for scalar functions, but it is a "best practice" generally. I would also caution against the over-use of scalar UDFs.  They can kill performance when incorrectly used.  And do not try to over-modularize your sql code - that can also kill performance.

[Former Member]

I'm not familiar with SQL Server but wouldn't you need a 'FROM' when you do a select in sql server?

In oracle, you have to do it like SELECT f_department(21) INTO :as_department FROM dual;

[Former Member]

Hi Polachan;

  FYI: You would not be using a SELECT statement with a DBMS function. Instead, have a look at the Execute DML command.

HTH

Regards ... Chris

[Former Member]

No Chris...  That advice would be correct if he were calling a stored procedure.  This is a user-defined function, so a SELECT statement is exactly how it should be coded.

Polachan, you must post the text of the error if anyone is going to be able to help.  Just telling us you received a "syntax error" isn't going to be useful at all.

-Paul-

[Former Member]

If the Function is returning a Result Set - then you are correct, a Select can be used.

However, you would have to use DUMMY ... ie:

Select select   f_department(21) into :as_depatment from Dummy;

If the FN did not return a RS - then you must use the EXEC command.

Thanks for pointing that out .... Happy US Thanksgiving BTW!

[Former Member]

That's a little closer, but still a bit off track...  I think you're still thinking of stored procedures. 

UDF's in SQLServer do not return result sets.  Ever.  They return scalar values. 

The SELECT statement they're included in can return a result set (or, in the case above, a singleton value because of the INTO clause).  If it returns a result set, then you're into cursor declarations and FETCH loops and all that stuff (or a datawindow...).  But the UDF itself is just providing a value for a single column on a single row.

So the difference isn't whether the UDF returns a resultset or not, since UDFs can't return a resultset in the first place.

-Paul-

[Former Member]

Thanks again ... yes, the better description is "singular" value. Once you have two or more columns or rows - then the RS processing option for a SF is not possible and you have to flip over to using a SP instead (with all the inherent cursor processing).

[Former Member]

Nope - UDFs can return scalar values or resultsets (really, tables).  E.g.,

CREATE FUNCTION [dbo].[uf_sequence] (@N int)

RETURNS @T TABLE (

   seq int not null primary key clustered

  )

...

[Former Member]

Hi Scott;

  Most DBMS's will not let you do that in a SF.

FYI: Sybase ASE  ... The query returns the name of the cursor in a Function, it does not output the rows of the result set.

Curiosity: Which DBMS ones are you referring to that will allow SF's to return RS's?

Regards .. Chris

[Former Member]

I cannot speak for "most dbms's", but sql server certainly supports table-valued functions - and has since 2005 (I think). I know OP has used sql server in the past, but the use of "DUAL" indicates otherwise.

[Former Member]

@Scott - yes, technically, that's correct.  SQLServer has table-valued functions.  But what I meant was that TVF's do not return the result set to the client directly.  They have to be referenced from an "outer" select statement.   And it's that SELECT statement that generates the resultset that gets returned to the client...

In other words, you don't say EXECUTE f_table_valued_function( args ); 

You say SELECT * from f_table_valued_function( args );   (or something to that effect)

-Paul-