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

What is the use of stored procedure?

512

---

What are the advantages dts has over bcp?

530

---

Can a stored procedure call itself or recursive stored procedure? How many levels sp nesting is possible?

521

---

What is partition index in sql server?

606

---

What is the difference between a local and a global temporary table?

622

---

---

How to copy the tables, schema and views from one sql server to another?

556

---

How to replace null values in expressions using isnull()?

647

---

What are click through reports?

99

---

What is the maximum row of a size?

572

---

What does man by sql wildcard characters in sql server?

521

---

What are types of scd? : sql server analysis services, ssas

540

---

What is indexed view?

597

---

What sql server means?

559

---

If any stored procedure is encrypted, then can we see its definition in activity monitor?

549

---

Does any body please help me what question's have asked for SSRS in the interview?

1666

---