Differences between functions and stored procedures?




Answers were Sorted based on User's Feedback



Differences between functions and stored procedures?..

Answer / avinash

1. Functions can used with Select statement
SP are not
2. If we have a syntax error or bug in SP it ignore it at
execution. In case of Function it won't.
3. Function should return atlease one value.
SP may or maynot return values.
4. Function cannot include in other function.
We can execute one SP in other.

Is This Answer Correct ?    9 Yes 4 No

Differences between functions and stored procedures?..

Answer / paras

UDF
1. Must return a value – a single result set
2. Directly used in select, order by,where, from
3. UDF can’t use nondeterministic function Such as
getdate(), rand()
4. Can’t change server enviroment variables
5. Stops execution of T-SQL code when error occurs
6. Can’t use temp table in UDF

Stored Procedure
1. Can return value – multiple result set
2. Can’t use in select
3. Can use nondeterministic functions
4. Can change server enviroment variables
5. Stored procedure move to the next instruction if you used
proper error handling,
6. Can use temp table in SP

Is This Answer Correct ?    6 Yes 2 No

Differences between functions and stored procedures?..

Answer / santhoshkumar.k

store procedure takes less time to execute the statement
than functions, also we can create loops inside the store
procedure.

Is This Answer Correct ?    4 Yes 0 No




Differences between functions and stored procedures?..

Answer / gautam

function does have update or delete statment.

Is This Answer Correct ?    3 Yes 0 No

Differences between functions and stored procedures?..

Answer / rathi

a stored procedure is like a subroutine. It is used to
perform tasks within the database, whether it be to INSERT,
UPDATE, DELETE, SELECT, send return values, send output
parameters, send e-mail, call command line arguments,
encapsulate business logic, enforce data integrity, or any
combination thereof. Here is a fictitious example:

CREATE PROCEDURE dbo.doStuff
@dt SMALLDATETIME
AS
BEGIN
SET NOCOUNT ON

DECLARE @un SYSNAME,
@now SMALLDATETIME

SET @un = SUSER_SNAME()
SET @now = GETDATE()

BEGIN TRANSACTION

INSERT dbo.myLog
(
ProcName,
UserName,
dt
)
SELECT
'doStuff',
@un,
@now

DELETE dbo.myLog
WHERE dt < (@now-7)

UPDATE dbo.Users
SET LastActivity = @now
WHERE UserName = @un

COMMIT TRANSACTION

SELECT TOP 3 ProcName, dt
FROM MyLog
WHERE UserName = @un
ORDER BY dt DESC

DECLARE @subject VARCHAR(255)
SET @subject = @un + ' used the doStuff procedure.'

EXEC master..xp_smtp_sendmail
@from = 'foo@bar.com',
@to = 'bar@foo.com',
@server = 'mail.myserver.com',
@subject = @subject

DECLARE @cmd VARCHAR(255)
SET @cmd = 'del c:\users\'+@un+'\archive\*.log'
EXEC master..xp_cmdshell @cmd, NO_OUTPUT

RETURN 0
END
GO

Stored procedures accept parameters, and are the preferred
method of both manipulating data and simply returning data.
They are compiled when first run, and the query plans are
stored and cached by SQL Server's optimizer, and those
cached plans are swapped out depending on frequency of
usage. Generally, a stored procedure will perform faster
than an ad hoc query, but there are certain cases (e.g.
when a bad plan is cached) that this is not the case.

User-Defined Functions (UDFs)

In general, UDFs can be a serious source of performance
issues. Also, UDFs cannot be used for DML operations
(INSERT/UPDATE/DELETE), cannot use non-deterministic
functions ,cannot use dynamic SQL, and cannot have error-
handling (e.g. RAISERROR).

Is This Answer Correct ?    2 Yes 0 No

Differences between functions and stored procedures?..

Answer / samba shiva reddy . m

1. Functions are compiled and executed at run time.
Stored procedures are stored in parsed and compiled format in the database(Pree compiled).

2. Functions cannot affect the state of the database which means we cannot perform insert,delete,update and create operations on the database.
Stored Procedures can affect the state of the database by using insert,delete,update and create operations.

3 Functions are basically used to compute values. We passes some parameters to functions as input and then it performs some operations on the parameter and return output.
Stored procedures are basically used to process the task.

4.Function can not change server environment and our operating system environment.
Stored procedures can change server environment and our operating system environment.

5.Functions can invoked from SQL Statements.
example : select udf from table name

Stored procedures can't invoked from SQL staements.
example : select spname from table name

6.Functions can run an executable file from SQL SELECT or an action query.
operating system use Execute or Exec to run

7.We can use User Defined function in Stored procedure
we can't use stored procedure in UDF.

Is This Answer Correct ?    2 Yes 0 No

Differences between functions and stored procedures?..

Answer / purushotam bhardwaj

FUnction use when you want to return any value only
but SP is best for all

Is This Answer Correct ?    1 Yes 0 No

Differences between functions and stored procedures?..

Answer / manoj pandey

Check following blog post for difference between UDF & Stored Procedures: http://sqlwithmanoj.wordpress.com/2011/09/21/stored-procedures-vs-functions-difference-between-sp-udf/

Is This Answer Correct ?    0 Yes 0 No

Post New Answer



More SQL Server Interview Questions

Ek lifafa 10 noto se bhara hai usme 2 or 5 ke note nahi hai aur usme total 50Rs hai, to batao lifafe me kon se not kitne hia it’s a challenge thank’s

5 Answers   Broadridge,


i have a table #temp1(id, Name groupname ) and record like this 1 R1 S 2 R3 S 3 R2 S 4 R4 D 5 R5 D 6 R6 K 7 R7 K 8 R8 L 9 R9 L 10 R10 L 11 R11 K and i want to display record based on user defind sorting order e.g. 1 R4 D 2 R5 D 3 R6 K 4 R7 K 5 R11 K 6 R1 S 7 R3 S 8 R2 S 9 R8 L 10 R9 L 11 R10 L

8 Answers  


Accidentally i deleted my table. How can i get that table?

4 Answers  


Which is the subset of SQL commands used to manipulate Oracle Database structures, including tables?

2 Answers  


how would you write a sql query to compute a frequency table of a certain attribute involving two joins? What changes would you need to make if you want to order by or group by some attribute? What would you do to account for nulls?

0 Answers   Facebook,






what is diffence between replicaion and logshipping?

1 Answers  


What are the differences between INNER JOIN, LEFT JOIN and RIGHT JOIN in SQL Server?

0 Answers   QuestPond,


What will happen when a Rollback statement is executed inside a trigger?

0 Answers   Flextronics, Hexaware,


Explain few examples of stored procedure over triggers?

0 Answers   ADITI,


What is the difference between MVC and Teir Architecher? Plz explain with Layyered Programming example...? Thanks

0 Answers   TCS,


How you can change the database name in SQL SERVER?

0 Answers  


What is better - 2nd Normal form or 3rd normal form? Why?

2 Answers   TCS, TPK,






Categories