SQL Server : stored procedure that calls another stored procedure

Question

EDIT:

Example in question might not be ideal for what I want,cause it always gives one result. Basically, I want a way to select all columns from one table plus a calculated value (Country in my case) where the id passed to that stored procedure is the itm.ID (Resulting in many rows)

Veera gave a working solution, I wonder if this can be done more efficiently with a stored procedure.

---

I'm a beginner using SQL Server, and I hope to get help with the following: I'm writing a stored procedure that calls another stored procedure.

The main stored procedure looks like this:

ALTER  PROCEDURE [Store].[usp_GetItem]
(
      @ID INT ,
      @Name NVARCHAR(255),
      @GroupNum INT
)   
AS
BEGIN
    SET NOCOUNT ON

    SELECT * 
    FROM Items itm
    WHERE itm.ID = @ID 
      AND itm.Name = @Name 
      AND itm.GroupNum = @GroupNum
END

Now, I have another stored procedure that looks like this:

ALTER  PROCEDURE Country
     (@ID INT) 
AS
BEGIN 
    SET NOCOUNT ON  

    DECLARE @CountryName NVARCHAR(255)
    Set @CountryName = SELECT Name
                       FROM General_c_Country 
                       WHERE ItemID = @ID  

    SELECT @CountryName
END

Now, I want the first procedure to pass the ID of the item (itm.ID) to the second procedure, get the result (the country name) and then the main stored procedure to display records of:

ID     Name        GroupNum   Country
-------------------------------------------------------------
1      Page          32       Spain (Country of Item with ID1

and so on...

Note: I know this can be simply done within one procedure with a simple inner join. Yet, I want to know how to do this in the way I have described. Thanks!

Answer 1

No need for the second SP. Instead use function like below.

CREATE FUNCTION Country( @ID INT)
RETURNS NVARCHAR(255)
  AS
BEGIN 
 SET NOCOUNT ON     
   DECLARE @CountryName NVARCHAR(255)
   Set @CountryName =SELECT Name
                     FROM General_c_Country 
                     WHERE ItemID = @ID  
   RETURN @CountryName
END

Call the function from the First SP as:

ALTER  PROCEDURE [Store].[usp_GetItem]
(
      @ID INT ,
      @Name NVARCHAR(255),
      @GroupNum INT
  )   
AS
BEGIN
    SET NOCOUNT ON

    SELECT *, dbo.Country(itm.ID) AS Country
    FROM Items itm
    where itm.ID = @ID AND itm.Name = @Name AND itm.GroupNum = @GroupNum
END

Answer 2

You can create a temporary table in the first procedure and then call the second procedure.The temporary table is recognized in the second procedure (since it is called by the first one and it is nested somehow). The second procedure should insert its value into the temporary table and when the execution returns to the first procedure, the temporary table is filled with data ready to be used.

Answer 3

you could go with a stored procedure with output parameters.

the code of the second stored procedure should look like this:

ALTER  PROCEDURE Country
     (@ID INT, @CountryNameParam NVARCHAR(255) OUTPUT) 
AS
BEGIN 
    SET NOCOUNT ON  

    SELECT @CountryNameParam = Name
    FROM General_c_Country 
    WHERE ItemID = @ID  

END

in the first stored procedure you retrieve the coutry name with this code:

declare @CountryName NVARCHAR(255)

exec Country @ID=[your id here], @CountryNameParam=@CountryName OUTPUT

print @CountryName

please note thet i used 2 different identifier names (@CountryName and @CountryNameParam) for the very same thing just to make clear who's who.

Answer 4

Why do you need a separate call in the first place? Why not just JOIN the tables and use the database in the way that it was intended? Both queries rely on the same @ID input param so it is a natural fit to do the following:

SELECT itm.*, gcc.Name AS [Country]
FROM Items itm
INNER JOIN General_c_Country gcc
        ON gcc.ItemID = itm.ID
WHERE itm.ID = @ID 
  AND itm.Name = @Name 
  AND itm.GroupNum = @GroupNum;

EDIT:
I just noticed the last line of the question that says you know that you can do this but want to know how to accomplish the specific question anyway. I should point out that your example output is not possible. You are passing in a single @ID to the first stored procedure and yet show two different IDs in the output. The two stored procedures you show in the question will only ever return a single row. What is the exact intention here as that will determine how to accomplish it. If you want multiple rows and hence multiple values from the second proc returned, then getting the country cannot be done in a stored procedure as it will be called once and cannot be part of the query, but it would work for a single row. If you need multiple values back then you either need to populate a local temp table with all of the possible values and JOIN on it (in which case why not just do the simple JOIN that I proposed), or you would need a scalar user-defined function (UDF) that takes in the @ID and returns the name.

Answer 5

first proc

  ALTER  PROCEDURE [Store].[usp_GetItem]
        (
              @ID INT output,
              @Name NVARCHAR(255),
              @GroupNum INT
        )   
        AS
        BEGIN
            SET NOCOUNT ON
        set @id=
            (SELECT id 
            FROM Items itm
            WHERE  
               itm.Name = @Name 
              AND itm.GroupNum = @GroupNum
return
    END

second proc

ALTER  PROCEDURE Country

AS
BEGIN 
    SET NOCOUNT ON  
declare @idx int
    DECLARE @CountryName NVARCHAR(255)
    Set @CountryName = SELECT Name
                       FROM General_c_Country 
                       WHERE ItemID =[Store].[usp_GetItem] [@Name],[@GroupNum],@ID =@idx output   

    SELECT @CountryName
END

insert into [@Name],[@GroupNum] the correct data