1. Can we crate USP(User defined Stored Procedure) on system databases also?
Yes, except "Resource" database
2. What are 3 main differences between EXEC and SP_ExecuteSQL ?
a) Batches b) Scope of names c) Database context
3. Can we create a USP with same name as already exist in Master DB?
Yes
3.If you have created a USP with same name as exist in system DB Master then which one will get executed first?
Master DB’s SP only executed
5.Which is better, embedded SQL call or using SP?
SP
6.How many types of USP can be created?
a) USP
b) Temporary (same as USP except stored in "tempdb")
On the basis of name, visibility and availability, Temporary USP, further classified as -
i) Local (#, to current user connection, auto deleted when connection is closed)
ii) Global (##, to any user after creation, auto deleted when the last session using proc ends)
7. When we use nested stored procedures ?
There may be many reasons for using Nested stored procedures like you want to call stored procedures from a parent stored procedure based on certain condition.
When a stored procedure is get called from a stored procedure it comes in context of Nested stored procedure.The nest level increases each time a stored procedure calls another and stopped when nesting level 32 reached.
It is used to break huge level of SQL code into smaller pieces.And it helps to find the mistakes if any and in modifications.