Can you call function within the parameters of a procedure?

1

I am trying pass values to a procedure to add a row. Can the parameters be a function whether system or user created scalar?

Example:
EXEC [dbo].[CreateGoodyearSOA] 'B','0000155658',getdate(),'101500',GetAllUnitsSold('232',getdate()),GetG3UnitsSold('232',getdate()),GetAllInvUnits('232',getdate()),' ',' ','*'

2

Call the functions before you execute the stored proc, save to variable and then use variable for stored proc exec.

3

I am very new to SQL. Can you show me an example of this?

4

declare @i int

select @i = dbo.somefunction('yada yada yada')

exec dbo.someproc @j = @i

5

Would this work?

DECLARE @TODAY1 AS DATE
@105All AS INT
@105G3 AS INT
@105INV AS INT
select @TODAY1 = GetDate()
select @105All = dbo.GetUnitsSoldLocationDate(@TODAY1,'105','Passenger, Light Truck','ALL')
select @105G3 = dbo.GetUnitsSoldLocationDate(@TODAY1,'105','Passenger, Light Truck','Goodyear,Kelly,Dunlop')
select @105INV = dbo.GetUnitsInvLocationPeriod('2016-01','105','Passenger, Light Truck','ALL')

EXEC [dbo].[CreateGoodyearSOA] 'B','0000198684',@TODAY1,'101500',@105All,@105G3,@105INV,'          ','                                                      ','*'
6

You need commas for your variable declarations. Give it a spin and let us know if you get errors.

7

I get the error when I call this procedure using the exec statement (when I just execute creating the procedure above I did not get an error, it said modified successfully), Cannot find either column "dbo" or the user-defined function or aggregate "dbo.GetUnitsSoldLocationDate", or the name is ambiguous. I created GetUnitsSoldLocationDate as a table valued function because I had to sum up units sold in another table. Is this correct? I am just not getting the function / procedure stuff.

Below is the function and the full procedure calling this function among others:

USE [TD_REPORTS]
GO
/****** Object: UserDefinedFunction [dbo].[GetUnitsSoldLocationDate] Script Date: 1/25/2016 3:06:00 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER FUNCTION [dbo].[GetUnitsSoldLocationDate]
(
-- Add the parameters for the function here
@sdate as date,
@location as varchar(50),
@class as varchar(100),
@mfg as varchar(100) )
RETURNS TABLE

AS
RETURN
(
-- Add the SELECT statement with parameter references here
SELECT SUM(Units) AS UnitsSold
FROM dbo.Sales_Detail
WHERE (DIM400 IN (@class) or @class = 'ALL') AND (DIM203 = 'REG-SALES') AND (DIM300 IN (@location) or @location = 'ALL') AND (DIM103 IN (@mfg) or @mfg = 'ALL') AND
(DIM900 = 'Tire') AND ([Inv-Date] = @sdate)

USE [TD_REPORTS]
GO
/****** Object: StoredProcedure [dbo].[GoodyearSOAData] Script Date: 1/25/2016 2:47:29 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Ross
-- Create date: 12-18-15
-- Description: Create record for Goodyear SOA
-- =============================================
ALTER PROCEDURE [dbo].[GoodyearSOAData]
-- Add the parameters for the stored procedure here

AS
BEGIN
DECLARE @TODAY1 AS DATE,
@105All AS INT,
@105G3 AS INT,
@105INV AS INT,
@232All AS INT,
@232G3 AS INT,
@232INV AS INT,
@311All AS INT,
@311G3 AS INT,
@311INV AS INT,
@316All AS INT,
@316G3 AS INT,
@316INV AS INT

select @TODAY1 = GetDate()
select @105All = dbo.GetUnitsSoldLocationDate(@TODAY1,'105','Passenger, Light Truck','ALL')
select @105G3 = dbo.GetUnitsSoldLocationDate](@TODAY1,'105','Passenger, Light Truck','Goodyear,Kelly,Dunlop')
select @105INV = dbo.GetUnitsInvLocationPeriod('2016-01','105','Passenger, Light Truck','ALL')
select @232All = [dbo].[GetUnitsSoldLocationDate](@TODAY1,'232','Passenger, Light Truck','ALL')
select @232G3 = [dbo].[GetUnitsSoldLocationDate](@TODAY1,'232','Passenger, Light Truck','Goodyear,Kelly,Dunlop')
select @232INV = [dbo].[GetUnitsInvLocationPeriod]('2016-01','232','Passenger, Light Truck','ALL')
select @311All = [dbo].[GetUnitsSoldLocationDate](@TODAY1,'311','Passenger, Light Truck','ALL')
select @311G3 = [dbo].[GetUnitsSoldLocationDate](@TODAY1,'311','Passenger, Light Truck','Goodyear,Kelly,Dunlop')
select @311INV = [dbo].[GetUnitsInvLocationPeriod]('2016-01','311','Passenger, Light Truck','ALL')
select @316All = [dbo].[GetUnitsSoldLocationDate](@TODAY1,'316','Passenger, Light Truck','ALL')
select @316G3 = [dbo].[GetUnitsSoldLocationDate](@TODAY1,'316','Passenger, Light Truck','Goodyear,Kelly,Dunlop')
select @316INV = [dbo].[GetUnitsInvLocationPeriod]('2016-01','316','Passenger, Light Truck','ALL')



EXEC [dbo].[CreateGoodyearSOA] 'A','00A0006591',@TODAY1,'101500','FN20151217','0000000000','0000000000','          ','                                                      ','*'
EXEC [dbo].[CreateGoodyearSOA] 'B','0000198684',@TODAY1,'101500',@105All,@105G3,@105INV,'          ','                                                      ','*'
EXEC [dbo].[CreateGoodyearSOA] 'B','0000155658',@TODAY1,'101500',@232All,@232G3,@232INV,'          ','                                                      ','*'
EXEC [dbo].[CreateGoodyearSOA] 'B','0000198698',@TODAY1,'101500',@311All,@311G3,@311INV,'          ','                                                      ','*'
EXEC [dbo].[CreateGoodyearSOA] 'B','0000167506',@TODAY1,'101500',@316All,@316G3,@316INV,'          ','                                                      ','*'
EXEC [dbo].[CreateGoodyearSOA] 'C','00A0006591',@TODAY1,'101500',@TODAY1,'0000000004','          ','          ','                                                      ','*'

END