sql server

[yogika]

Is these differences are correct or not as interviewer said me these are incorrect....please guide me... Difference between function and SP

Procedure can return zero or n values whereas function can return one value which is mandatory.
Procedures can have input/output parameters for it whereas functions can have only input parameters.
Procedure allows select as well as DML statement in it whereas function allows only select statement in it.
Functions can be called from procedure whereas procedures cannot be called from function.
Exception can be handled by try-catch block in a procedure whereas try-catch block cannot be used in a function.

[Lost User]

Sounds/looks correct, at least for TSQL. You could take the time to verify each statement on a PC, and mail the recruiter the results :)

Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] They hate us for our freedom![^]

[Richard Deeming]

1. Wrong; a table-valued function[^] may return more than one value as part of a result-set;

2. Correct; functions may not have OUTPUT parameters;

3. Wrong; a multi-statement UDF may contain[^]:

   • assignment statements;
   • control-of-flow statements (except TRY...CATCH);
   • DECLARE statements;
   • SELECT statements;
   • cursor operations referencing local cursors;
   • INSERT, UPDATE and DELETE statements modifying local table variables; and
   • EXECUTE statements calling extended stored procedures[^];

4. Sort-of; a function may call an extended stored procedure[^], but not a regular stored procedure;

5. Correct; a function may not contain a TRY...CATCH block;

---

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

[Lost User]

Nice - all by memory? :-D I should've known the first, disagree with the fourth. Point three I'd expect that the function does not allow DDL, only DML.

Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] They hate us for our freedom![^]

[Richard Deeming]

Almost - I had to look a couple up to confirm what I thought. Why would you disagree with #4 when it's documented in BOL[^]?

The following statements are valid in a function: ... EXECUTE statements calling extended stored procedures.

---

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

[Lost User]

Richard Deeming wrote:

Almost - I had to look a couple up to confirm what I thought.

Just a little validation - impressive :thumbsup:

Richard Deeming wrote:

Why would you disagree with #4 when it's documented in BOL[^]?

I wouldn't wanna throw SPs and XSPs on a single heap. One is a script, the other is code.

Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] They hate us for our freedom![^]

[PIEBALDconsult]

They're apples and oranges.

[PIEBALDconsult]

5. They might in the future.

[Lost User]

Exactly, just like the question and your answer.