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).

Can we use custom code in ssrs?

775

---

What are the advantages of log shipping?

733

---

How to count groups returned with the group by clause in ms sql server?

737

---

How to change the data type of an existing column with "alter table" statements in ms sql server?

729

---

How to handle error or exception in sql?

700

---

---

can you instantiate a com object by using t-sql? : Sql server database administration

713

---

Describe in brief databases and sql server databases architecture.

680

---

What is 2nf normalization form?

776

---

What is the sql case statement used for? Explain with an example?

711

---

When is update_statistics command used?

766

---

What is index, cluster index and nonclustered index?

685

---

What is a field in a table?

775

---

What is the difference between composite index and covering index?

683

---

Explain tables in SQL Azure?

114

---

Explain what are page splits? : SQL Server Architecture

812

---