SQL stored procedure call Stored Procedure

Studying a project code made by someone else, we can see that there is a piece of stored procedure calling code in the database. The original code is complicated. So I plan to write a simple example to learn it. 1. Create a stored procedure to be called. USE [MSPetShop4] The PetShop's ready-made database GOALTERPROCEDURE [dbo].

Studying a project code made by someone else, we can see that there is a piece of stored procedure calling code in the database. The original code is complicated. So I plan to write a simple example to learn it. 1. Create a stored procedure to be called. USE [MSPetShop4] // USE the PetShop's ready-made database go alter procedure [dbo].

Studying a project code made by someone else, we can see that there is a piece of stored procedure calling code in the database. The original code is complicated. So I plan to write a simple example to learn it.

1. Create a stored procedure to be called.

USE [MSPetShop4] // a ready-to-USE PetShop Database
GO

Alter procedure [dbo]. [uspGetCategoryID]
@ Name varchar (20 ),
@ CateID varchar (20) output // Add output variable and output tag
AS
BEGIN
-- Set nocount on added to prevent extra result sets from
-- Interfering with SELECT statements.
SELECT @ CateID = [CategoryId] // assign values to output variables

FROM [MSPetShop4]. [dbo]. [Category] where Name = @ Name
END

Ii. Call the Stored Procedure

USE [MSPetShop4]
GO

ALTER procedure [dbo]. [saveProduct]
(
@ Prodid char (20 ),
@ Catname char (20 ),
@ ProdName char (20)
)
As
Begin
Set nocount on
Declare @ CategoryID varchar (20) // variable used to store the output result
Exec dbo. uspGetCategoryID @ Name = @ catname, @ CateID = @ CategoryID output // the original code is two variables with the same name. I changed the name to a different one and found that the called stored procedure result variable = the variable name to be received different from what I think

Select @ CategoryID

Insert into Product values (@ prodid, @ CategoryID, @ ProdName ,'','')
End