Can a stored procedure call itself or recursive stored procedure? How much level SP nesting is possible?

Yes. Because Transact-SQL supports recursion, you can write stored procedures that call themselves. Recursion can be defined as a method of problem solving wherein the solution is arrived at by repetitively applying it to subsets of the problem. A common application of recursive logic is to perform numeric computations that… Continue reading

What are the authentication modes in SQL Server? How can it be changed?

Windows mode and Mixed Mode – SQL & Windows. To change authentication mode in SQL Server click Start, Programs, Microsoft SQL Server and click SQL Enterprise Manager to run SQL Enterprise Manager from the Microsoft SQL Server program group. Choose the server then from the Tools menu select SQL Server… Continue reading

What are the advantages of using Stored Procedures?

Stored procedure can reduced network traffic and latency, boosting application performance. Stored procedure execution plans can be reused, staying cached in SQL Server’s memory, reducing server overhead. Stored procedures help promote code reuse. Stored procedures can encapsulate logic. You can change stored procedure code without affecting clients. Stored procedures provide… Continue reading