Error with SQL stored procedure output type I have a table 2 tables, namely DAH_Errors and Intraday_Errors, and I want to calculate some statistical performance indicators, fore example the NMAE over a specified range of rows. The range is specified by 2 variables @fromdate and @todate, as shown below:

DECLARE @fromdates VARCHAR(20)='2017-12-11 00:15:00'
DECLARE @todates VARCHAR(20)='2017-12-12 00:00:00'
DECLARE @fromdate SMALLDATETIME=CAST(@fromdates AS SMALLDATETIME)
DECLARE @todate SMALLDATETIME=CAST(@todates AS SMALLDATETIME)

DECLARE @nmae1 DECIMAL(20,5)
DECLARE @nmae DECIMAL(20,5)
DECLARE @nrmse1 DECIMAL(20,5)
DECLARE @nrmse DECIMAL(20,5)


In order to calculate the NMAE, I have created a the procedure get_nmaev2, which is the following:

CREATE PROCEDURE [dbo].[get_NMAEv2]
        @forecasttype VARCHAR(MAX),
        @fromdate2 SMALLDATETIME,
        @todate2 SMALLDATETIME,
        @nmae DECIMAL(20,5) OUTPUT
AS
SET NOCOUNT ON;

DECLARE @aux VARCHAR(MAX)='Hello'
IF @forecasttype='DAH'
    SET @aux='SELECT '+@nmae+'=AVG(ABS(['+@forecasttype+'_Errors].[Forecast]-['+@forecasttype+'_Errors].[Metering])/CAST(['+@forecasttype+'_Errors].[Capacity] AS DECIMAL(20,5)))
    FROM dbo.'+@forecasttype+'_Errors
    WHERE ['+@forecasttype+'_Errors].[DateTime(UTC)]&gt;='+@fromdate2+' AND ['+@forecasttype+'_Errors].[DateTime(UTC)]&lt;='+@todate2+' AND ['+@forecasttype+'_Errors].[NAE] IS NOT NULL;'
    EXEC(@aux)

IF @forecasttype='Intraday'
    SET @aux='SELECT '+@nmae+'=AVG(ABS(['+ @forecasttype+'_Errors].[Forecast]-['+@forecasttype+'_Errors].[Onlline])/CAST(['+@forecasttype+'_Errors].[Capacity] AS DECIMAL(20,5)))
    FROM dbo.'+@forecasttype+'_Errors
    WHERE ['+@forecasttype+'_Errors].[DateTimeUTC]&gt;='+@fromdate2+' AND ['+@forecasttype+'_Errors].[DateTimeUTC]&lt;='+@todate2+' AND ['+@forecasttype+'_Errors].[NAE] IS NOT NULL;'
    EXEC(@aux)


The variables @fromdate2,@todate2 are used in the WHERE statement in order to specify the range over which I will calculate the NMAE. 

I then execute the following, in order to get my result:

EXEC dbo.get_nmaev2
     @forecasttype='DAH',  
     @fromdate2 =  @fromdate,
     @todate2 = @todate,
     @nmae=@nmae1 OUTPUT


What I want to do, is to store value of the select statement in the variable @nmae, and output that to the variable @nmae1, shown in the first script where I call execute the procedure. However, I get the following error message:

Msg 8114, Level 16, State 5, Procedure get_NMAEv2, Line 16
Error converting data type varchar to numeric.


I can not find what causes the error, so I would really appreciate some help here. Does this happen because I am trying to include the variable @nmae in the dynamic SQL statement;

I know what the error itself actually means, but I simply cant find what causes it :)
This row causes your error:


SET @aux='SELECT '+@nmae



Since @nmae is DECIMAL(20,5) there is no way to make a sum without converting, and thanks to type precedence, it's string SELECT that should be converted to numeric, and this is impossible.

Here is quick repro:

declare @nmae DECIMAL(20,5); 

DECLARE @aux VARCHAR(MAX)='Hello'

SET @aux='SELECT '+@nmae;

exec(@aux);


To see what is converted to what you can use this query:

select '2' + 2


this will not produce '22', but 4, as it's tring '2' to be converted to numeric, not viceversa.

UPDATE

You can use synonym to get rid of all your dynamic code like this:

declare @forecasttype VARCHAR(MAX) = 'DAH'
declare @sql varchar(8000) = 'create synonym dbo.MySyn for ['+@forecasttype+'_Errors]';
exec(@sql); -- this will create MySyn that points to your table


Now you can use the created synonym instead of your table:

SELECT @nmae=AVG(ABS([MySyn].[Forecast]-[MySyn].[Metering‌​])/CAST([MySyn]‌​.[Capacity] AS DECIMAL(20,5))) from dbo.[MySyn]


And it's even better create an alias to not rewrite table name every time in the select:

SELECT @nmae=AVG(ABS(s.[Forecast]-s.[Metering‌​])/CAST(s.[Capacity] AS DECIMAL(20,5))) from [MySyn] s


At the end of sp just drop the synonym.

You should be sure that this sp is not executed at the same time by more than 1 user, if it's so you need other solution