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.

OT: [Read about a *bad habit to kick*: Declaring (N)VARCHAR without a length](https://sqlblog.org/2009/10/09/bad-habits-to-kick-declaring-varchar-without-length) — Shnugo, Oct 28 '16 at 08:11
I do not understand your goal: If you just **read** data, why are you creating a *stored procedure* (which is meant **to do** something)? What is wrong with a row? What is better with *variables* (especially when the title carries *set-based*)? Please provide some more details! — Shnugo, Oct 28 '16 at 08:13
The procedure is way bigger then this. And it reads much more data then just this. And it then writes it off to a database — Thimo Franken, Oct 28 '16 at 08:16

score 2 · Accepted Answer · answered Oct 28 '16 at 08:38

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.

score 0 · Answer 2 · edited Oct 29 '16 at 15:59

0

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

edited Oct 29 '16 at 15:59

VDWWD

35,079
22
62
79

answered Oct 28 '16 at 08:30

Lars Skogshus

56
7

Stored procedure get data from query SET-based

2 Answers2