1. What is Stored procedure?
o They allow modular programming.
o They allow faster execution.
o They can reduce network traffic.
o They can be used as a security mechanism.
2. What are the different types of Storage Procedure?
a. Temporary Stored Procedures - SQL Server supports two types of temporary procedures: local and global. A local temporary procedure is visible only to the connection that created it.
A global temporary procedure is available to all connections. Local temporary procedures are automatically dropped at the end of the current session. Global temporary procedures are dropped at the end of the last session using the procedure. Usually, this is when the session that created the procedure ends. Temporary procedures named with # and ## can be created by any user.
b.    System stored procedures are created and stored in the master database and have the sp_ prefix.(or xp_) System stored procedures can be executed from any database without having to qualify the stored procedure name fully using the database name master. (If any user-created stored procedure has the same name as a system stored procedure, the user-created stored procedure will never be executed.)
c.    Automatically Executing Stored Procedures - One or more stored procedures can execute automatically when SQL Server starts. The stored procedures must be created by the system administrator and executed under the sysadmin fixed server role as a background process. The procedure(s) cannot have any input parameters.
d.    User stored procedure
3.    How do I mark the stored procedure to automatic execution?
USE master
4.    How can you optimize a stored procedure?
5.    How will know whether the SQL statements are executed?
6.    Why one should not prefix user stored procedures with sp_?
0.    The stored procedure in the master database.
1.    The stored procedure based on any qualifiers provided (database name or owner).
0.    Server restart
1.    Plan is aged out due to low use
2.    DBCC FREEPROCCACHE (sometime desired to force it)
8.    When do one need to recompile stored procedure?
9.    SQL Server provides three ways to recompile a stored procedure:
·    The sp_recompile system stored procedure forces a recompile of a stored procedure the next time it is run.
·    Creating a stored procedure that specifies the WITH RECOMPILE option in its definition indicates that SQL Server does not cache a plan for this stored procedure; the stored procedure is recompiled each time it is executed. Use the WITH RECOMPILE option when stored procedures take parameters whose values differ widely between executions of the stored procedure, resulting in different execution plans to be created each time. Use of this option is uncommon, and causes the stored procedure to execute more slowly because the stored procedure must be recompiled each time it is executed.
10.    How to find out which stored procedure is recompiling? How to stop stored procedures from recompiling?

commit tran
Both will get roll backed.
    commit tran
Both will get roll backed.
13.    How will you handle Errors in Sql Stored Procedure?
INSERT NonFatal VALUES (@Column2)
IF @@ERROR <>0
  PRINT 'Error Occured'
14.    How will you raise an error in sql?
15.    I have a stored procedure like
commit tran
create table a()
insert into table b
rollback tran
16.    What do you do when one procedure is blocking the other?
17.    How you will return XML from Stored Procedure?
18.    What are the differences between RAW, AUTO and Explicit modes in retrieving data from SQL Server in XML format?
USE master
IF OBJECT_ID('dbo.sp_calcfactorial') IS NOT NULL
DROP PROC dbo.sp_calcfactorial
CREATE PROC dbo.sp_calcfactorial
@base_number int, @factorial int OUT
DECLARE @previous_number int
IF (@base_number<2) SET @factorial=1 -- Factorial of 0 or 1=1
SET @previous_number=@base_number-1
EXEC dbo.sp_calcfactorial @previous_number, @factorial OUT -- Recursive call
IF (@factorial=-1) RETURN(-1) -- Got an error, return
SET @factorial=@factorial*@base_number

calling proc.
DECLARE @factorial int
EXEC dbo.sp_calcfactorial 4, @factorial OUT
SELECT @factorial
20.    Nested Triggers
22.    Difference between view and stored procedure?
23.    What is a Function & what are the different user defined functions?
Function is a saved Transact-SQL routine that returns a value. User-defined functions cannot be used to perform a set of actions that modify the global database state. User-defined functions, like system functions, can be invoked from a query. They also can be executed through an EXECUTE statement like stored procedures.
0.    Scalar Functions
Functions are scalar-valued if the RETURNS clause specified one of the scalar data types
1.    Inline Table-valued Functions
If the RETURNS clause specifies TABLE with no accompanying column list, the function is an inline function.
2.    Multi-statement Table-valued Functions
If the RETURNS clause specifies a TABLE type with columns and their data types, the function is a multi-statement table-valued function.
24.    What are the difference between a function and a stored procedure?

1.    Procedure takes both input and output parameters but Functions takes only input parameters
2.    Functions cannot return values of type text, ntext, image & timestamps where as procedures can
3.    Functions can be used as user defined datatypes in create table but procedures cannot
***Eg:-create table <tablename>(name varchar(10),salary getsal(name))
Here getsal is a user defined function which returns a salary type, when table is created no storage is allotted for salary type, and getsal function is also not executed, But when we are fetching some values from this table, getsal function get’s executed and the return
Type is returned as the result set.
How to debug a stored procedure?