Differences between functions and stored procedures?
- 8 Answers
- 13099 Views
- 247Customer, Accenture, I also Faced
- E-Mail Answers
Answer Posted / 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 |
Post New Answer View All Answers
How to delete duplicate rows from table except one?
What is the primary use of the model database?
Why we should not use triggers?
What do you understand by replication in sql server?
How to define and use table alias names in ms sql server?
When to use Inner join & when to use subquery?
How to create “dependant” parameter “make, model, year”
can an automatic recovery be initiated by a user? : Sql server administration
What are different types of raid levels?
What is an execution plan?
What is the difference between indexing and hashing?
What is "scheduled jobs" or "scheduled tasks"?
If a user does not have permission to a table, but has permission to a view created on it, will he be able to view the data in table?
What is scalar user-defined function?
What is blocking and how would you troubleshoot it? : sql server database administration
