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

Explain the microsoft sql server delete command?

1024

---

‘Order by’ is not allowed in a view how can you sort information from a view?

1085

---

How to write character string constants or literals in ms sql server?

1026

---

wat wil hapn if we give the both read and deny read permission to user?

2103

---

How do I perform an unattended install of sql server 2000?

1086

---

Can sql servers link to other servers like oracle?

843

---

What is change data capture (cdc) feature?

1069

---

How does clustered and non clustered index work?

925

---

What is the openxml statement in sql server?

1075

---

Why use cursor in sql server?

1081

---

Define cross join in sql server joins?

1022

---

What are the differences between ms sql server & oracle?

1063

---

Do you know how to make remote connection in database?

965

---

What is server-level principal?

122

---

How do I get to sql server configuration manager?

1010

---