Stored Procedure

ch chandrakala

7y1.2kSQL

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

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Santhosh Subramaniam6y ago
Hi,
SP takes advantage of the following,
1. Coding Principles,
2. One Place processing
3. Easy Test
4. Maintaniability
5. Speed
6. Security
Mageshwaran R6y ago
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 Gupta7y ago
Hi,

Visit the following link for detail -

https://www.sqlservercentral.com/articles/nesting-stored-procedures
Amit Kumar7y ago
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.
Vincent Maverick Durano7y ago
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.
Abhijeet Jadhav7y ago
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.