Stored Procedure

Question

Why we need to write stored procedure with in another stored procedure?What is the use?

Vincent Maverick Durano · Answer

Just like any code. It's recommended to write stored procedure that handle specific job (separation of concerns). This way it's very easy for you to maintain, add or remove logic without impacting your whole script. Think of it as wrting a separate method in your app that calls another method to perform certain tasks. The idea is to minimize code complexity within a method by splitting them up to multiple methods or stored procedures in your case.

Santhosh Subramaniam · Answer

Hi, SP takes advantage of the following, 1. Coding Principles, 2. One Place processing 3. Easy Test 4. Maintaniability 5. Speed 6. Security

Mageshwaran R · Answer

Hi chandrakala, StoreProcedure: 1.store procedure is used to multiple queries execute a single execution. 2.They allow faster execution. 3.They can reduce network traffic 4.store procedure we can used to input and output parameters. Example: CREATE PROCEDURE TEST_SP_NAME(@ID INT ,@ NAME NVARCHAR(50),@TRANSACTTYPE VARCHAR (1)) AS BEGIN IF(@TRANSACTTYPE= 'I' ) BEGIN INSERT INTO TEST_TABLE_NAME(ID, NAME ) VALUES (@ID,@ NAME ) END ELSE IF(@TRANSACTTYPE= 'U' ) BEGIN UPDATE TEST_TABLE_NAME SET NAME =@ NAME WHERE ID=@ID END ELSE BEGIN SELECT * FROM TEST_TABLE_NAME END END EXEC TEST_SP_NAME 1, 'M' , 'I'

Sonu Gupta · Answer

Hi, Visit the following link for detail - https://www.sqlservercentral.com/articles/nesting-stored-procedures

Amit Kumar · Answer

Hi, If you are writing a proc and it is two much large , let too much large procedure is not readable and understand by others . So in simple way if you have break into multiple then it is easy for write and understandable for every one . So it will need.

Abhijeet Jadhav · Answer

There are various reasons to use a stored procedure within a stored procedure: i) It helps in modularity, which helps when issue arises ii) It improves data integrity as there is fixed input and output of a stored proc. iii) Since stored procedures are stored in pre-compiled format, performance is faster as compared to SQL queries.