Call a stored procedure from a stored procedure

Question

I have a stored procedure that gets a photo file from the database . It returns the name (mypic.png) and height and with in pixels

I have several SPs that pull different users and I would like to get the photo info for each by calling the getuserpic from the different SPs. I tried this -

USE [BDUC]
GO
/****** Object:  StoredProcedure [dbo].[getmuttsdailyride]    Script Date: 10/04/2013 20:49:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[getmuttsdailyride] 
	-- Add the parameters for the stored procedure here
	@riderid int
,	@DAY DATETIME
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	DECLARE @photoname varchar(50) output
,	DECLARE  @height int output
,	DECLARE  @width int	output
	EXEC fetchriderpic @riderid, @photoname output,@height output, width output
	SET NOCOUNT ON;

    -- Insert statements for procedure here
select ridedate, ml.riderid, r.dname,
[distance] = (select Case when ml.distanceunit = 'K' then ml.distancekilos else ml.distancemiles end), ml.distanceunit
,description 
, @photoname
, @height
, @width
FRom mileagelog ml 
join riders r On r.riderid = ml.riderid 
where ml.riderid in( select targetriderid from mutt where riderid = @riderid)
AND ml.ridedate between  DATEADD(dd,-5,@DAY) and @DAY 
END

which gives me alot of errors. Can this be done? Thx in advance!

Answer 1

try this, corrected few syntax errors.

ALTER PROCEDURE [dbo].[getmuttsdailyride] 
	-- Add the parameters for the stored procedure here
	@riderid int
,	@DAY DATETIME
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	DECLARE @photoname varchar(50) 
	DECLARE  @height int 
	DECLARE  @width int	
	EXEC fetchriderpic @riderid, @photoname output,@height output, @width output
	SET NOCOUNT ON;

    -- Insert statements for procedure here
select ridedate, ml.riderid, r.dname,
[distance] = (select Case when ml.distanceunit = 'K' then ml.distancekilos else ml.distancemiles end), ml.distanceunit
,description 
, @photoname
, @height
, @width
FRom mileagelog ml 
join riders r On r.riderid = ml.riderid 
where ml.riderid in( select targetriderid from mutt where riderid = @riderid)
AND ml.ridedate between  DATEADD(dd,-5,@DAY) and @DAY 
END

---

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 2

That does execute the SP alright, but I guess I should have seen that it would not put those values into

ALTER PROCEDURE [dbo].[getmuttsdailyride] 
	-- Add the parameters for the stored procedure here
	@riderid int
,	@DAY DATETIME
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	DECLARE @riderinf table
	(
		Rider nvarchar(50)
		, Distance float
		, Scale nvarchar(10)
		, Comment nvarchar(max)
		, Photofile nvarchar(50)
		, Height int
		, Width int
	)
	DECLARE @photoname varchar(50) 
	DECLARE  @height int 
	DECLARE  @width int	
	EXEC fetchriderpic @riderid, @photoname output,@height output, @width output
	SET NOCOUNT ON;
	insert into @riderinf (Rider, Distance, Scale, Comment, Photofile, Height, Width)values (null,null,null,null,@photoname,@height,@width)
    -- Insert statements for procedure here
    --select * from @riderinf
    
select ridedate, ml.riderid, r.dname,
[distance] = (select Case when ml.distanceunit = 'K' then ml.distancekilos else ml.distancemiles end), ml.distanceunit
,description 
, @photoname
, @height
, @width
FRom mileagelog ml 
join riders r On r.riderid = ml.riderid 
where ml.riderid in( select targetriderid from mutt where riderid = @riderid)
AND ml.ridedate between  DATEADD(dd,-5,@DAY) and @DAY 
END

the result set obtained by the select. How do I get the values returned by the SP into one result set? I am guessing that I will have to use a temptable and union?

Answer 3

the result set obtained by the select. How do I get the values returned by the SP into one result set? I am guessing that I will have to use a temptable and union?

Hi Dave,

According to your code, we are confused that which one of stored procedure need to be inserted into the one result. If you want to perform the fetchriderpic  to get the output values then insert into the template table @riderinf, the getmuttsdailyride stored procedure which you post can run well. So we need you post more details about your table structure, requirements, explains for analysis.  

Thanks,
Sofiya Li

---

Sofiya Li
TechNet Community Support

Answer 4

We can insert into a temporary table.

INSERT INTO #TEMP(COL1,COL2,COL3) EXEC 'PARAM1','PARAM2'

-- Provided that the return result set has the same number and datatype column as the destination temporary table.

---

Regards, RSingh

Answer 5

Hi Dave, Is this still an issue ?

---

Regards, RSingh