Stored procedure get data from query SET-based

Question

Hello I am making a stored procedure and I am having problem outputting the code with a print because later on i need to write the variables to a temp database. This is the code:

SELECT 
    fmsTotalAmount + fmsAirTotalAmount + fmsProjectsTotalAmount TotalAmount, 
    fmsRelationAmount + fmsAirRelationAmount + fmsProjectsRelationAmount TotalRelationAmount
    FROM (

        SELECT 
            SUM(
                CASE WHEN fms1.currency != 'EUR' 
                    THEN fms1.Amount * fms1.Rate
                    ELSE ISNULL(fms1.Amount, 0) END) fmsTotalAmount,
            SUM(
                CASE WHEN fms1.relationcode = 'SHIP02' 
                    THEN 
                        CASE WHEN fms1.currency != 'EUR' 
                            THEN fms1.Amount * fms1.Rate
                            ELSE ISNULL(fms1.Amount, 0) END
                    ELSE 0 END) fmsRelationAmount,
            SUM(
                CASE WHEN fmsAir1.currency != 'EUR' 
                    THEN fmsAir1.Amount * fmsAir1.Rate
                    ELSE ISNULL(fmsAir1.Amount, 0) END) fmsAirTotalAmount,
            SUM(
                CASE WHEN fmsProjects1.relationcode = 'SHIP02' 
                    THEN 
                        CASE WHEN fmsProjects1.currency != 'EUR' 
                            THEN fmsProjects1.Amount * fmsAir1.Rate
                            ELSE ISNULL(fmsProjects1.Amount, 0) END
                    ELSE 0 END) fmsAirRelationAmount,
            SUM(
                CASE WHEN fmsProjects1.currency != 'EUR' 
                    THEN fmsProjects1.Amount * fmsAir1.Rate
                    ELSE ISNULL(fmsProjects1.Amount, 0) END) fmsProjectsTotalAmount,
            SUM(
                CASE WHEN fmsProjects1.relationcode = 'SHIP02' 
                    THEN 
                        CASE WHEN fmsProjects1.currency != 'EUR' 
                            THEN fmsProjects1.Amount * fmsProjects1.Rate
                            ELSE ISNULL(fmsProjects1.Amount, 0) END
                    ELSE 0 END) fmsProjectsRelationAmount
        FROM   [fms].[dbo].[file] f
        LEFT JOIN [fms].[dbo].[outgoinginvoiceline] fms1 ON 
            fms1.filenumber = CONVERT(NVARCHAR, f.filenumber)
        LEFT JOIN [fmsAir].[dbo].[outgoinginvoiceline] fmsAir1 ON 
            fmsAir1.filenumber = CONVERT(NVARCHAR, f.filenumber)
        LEFT JOIN [fmsProjects].[dbo].[outgoinginvoiceline] fmsProjects1 ON 
            fmsProjects1.filenumber = CONVERT(NVARCHAR, f.filenumber)


) a

Now i want to print the output of this SELECT statement. But when i do this:

print 'Total money invoiced: ' + CONVERT(nvarchar, fmsTotalAmount) + '€ for this Relation' print 'Total money invoiced: ' + CONVERT(nvarchar, fmsTotalRelationAmount) + '€ in total'

But then i get the following error:

The name "fmsTotalAmount" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

The name "fmsTotalRelationAmount" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

On the prints. How do I get the values from this into a variable? Instead of it returning it as a row. I need the variables.

Answer 1

If I understand this correctly, you want to store the result of a SELECT into a table.

To achieve this, you try to write the values into variables first and then - in a second step - you try to insert these variables into a table with an INSERT statement.

Better was to try this syntax:

SELECT col1, col2, ... INTO #SomeTable FROM SomeWhere

The INTO #SomeTable will create a temp table with the fitting structure automatically and insert the full result into this newly created temp table.

A simple SELECT * FROM #SomeTable would bring back the result.

Answer 2

Try to declare a printvariable first and print that value.

declare @ptext varchar(max)
select @ptext = 'Total money invoiced: ' + CONVERT(nvarchar, fmsTotalAmount) + '€ for this Relation' 
print @ptext
select @ptext = 'Total money invoiced: ' + CONVERT(nvarchar, fmsTotalRelationAmount) + '€ in total'
print @ptext