Re: Interview Questions Feb 01 2006

Jobs wrote:
> What's the difference between Stored Procedure (SP) and User Defined
> Function (UDF)?

In what language? On what OS?  What DBMS?

> Following are some major differences between a stored procedure and
> user defined functions:-
> 1)UDF can be executed using the "SELECT" clause while SP's can
> not be.

ok true for some databases but not for all. e.g.,
ORACLE true
UNIFY Dataserver  false

> 2)UDF can not be used in XML FOR clause but SP's can be used.

still depends on the DB

> 3)UDF does not return output parameters while SP's return output
> parameters.

Depends again
in ORACLE the UDF can have OUT parameters.

> 4)If there is an error in UDF its stops executing. But in SP's it
> just ignores the error and moves to the next statement.

FALSE in ORACLE. Both throw an exception. If the body includes an
exception handler, control passes to there, otherwise control passes to
the next higher level, and the process repeats.

> 5)UDF can not make permanent changes to server environments while
> SP's can change some of the server environment.

again, depends on the DBMS. A function in ORACLE can change server
environment (update tables) just as easily as a procedure can. Note
that if it does such things, it cannot be used in SQL statements like
SELECT any more.

>
>
> Full Interview Questions for .NET and SQL Server

OH now you let us know, this is all SQL Server. Thanks for the heads
up.


> []
Ad deleted

.

Relevant Pages

  • Re: Invalid directory path / service name
    ... I am facing a "Invalid directory path" problem. ... did you run the stored procedure when you got the error on ... Did you look up the error in the Oracle Server messages manual? ...
    (comp.databases.oracle.misc)
  • Re: Linked server to Oracle
    ... execute stored procedure insided the trigger. ... >From SQL Server insert a row into this tableso ... that trigger will get fired and subsequently oracle stored procedure. ...
    (microsoft.public.sqlserver.security)
  • Re: Calling an Oracle stored procedure
    ... > Is there any way to call an Oracle 10g stored procedure from a SQL ... I am relatively new to SQL Server so ... Whether that actually with Oracle procedures, ...
    (microsoft.public.data.oledb)
  • Re: SQL/ADO (2.8) Timeout Error - Cant resolve
    ... > Server 2000 back end. ... > timeout limit. ... And I’ve used SQL Query Analyzer to run the ... I am running a stored procedure that will backup a SQL Server ...
    (microsoft.public.sqlserver.clients)
  • Re: How to test a custom conflict resolver procedure
    ... once I set my stored procedure as a custom resolver? ... Here is an example of a custom resolver I wrote which might help you ... --creating the stored procedure conflict resolver ... Looking for a SQL Server replication book? ...
    (microsoft.public.sqlserver.replication)