sql stored procedure, select into

Question

i try to build a store procedure who insert data to a table, after it run, the table is empty.

this is the code:

CREATE TABLE invoices
(invoiceNo int,
 invoiceDate date,
 invoiceTotal int,
 invoiceType char(1))


alter PROCEDURE Invoices_AGG
@year int

AS
    select
    (case when MONTH(invoiceDate) >9 then concat('01','/',MONTH(invoiceDate),'/',year(invoiceDate)) else concat('01','/0',MONTH(invoiceDate),'/',year(invoiceDate)) end) as DateID,
    SUM(case when invoiceType = 'B' then invoiceTotal else 0 end) as Total_Incomes_TypeB,
    SUM(case when invoiceType = 'I' then invoiceTotal else 0 end) as Total_Incomes_TypeI
    into FACT_Invoices_AGG 
    from invoices
    where year(invoiceDate)=@year
    group by (case when MONTH(invoiceDate) >9 then concat('01','/',MONTH(invoiceDate),'/',year(invoiceDate)) else concat('01','/0',MONTH(invoiceDate),'/',year(invoiceDate)) end);


exec Invoices_AGG 2013

thank you

We need to know which database you are using, is it SQL Server, Oracle, MySQL? Please tag accordingly. — EzLo, Mar 22 '18 at 07:52
@JOE, which dbms? That code is not ANSI SQL, which the tag is for. — jarlh, Mar 22 '18 at 07:54
Is there data in the version of the table invoices that you're using? And is any of that data from 2013? — Eric Brandt, Mar 22 '18 at 11:09

score 0 · Answer 1 · answered Mar 22 '18 at 07:59

0

you haven't specified the table in which you want to insert data and since your table is empty on which you are applying your select query it's returning no result. If you want to use

into

you should also mention your code for FACT_Invoices_AGG function.

answered Mar 22 '18 at 07:59

Kamran

147
2
12

What function are you talking about? FACT_Invoices_AGG is the target table for the OP's SELECT...INTO statement. – Eric Brandt Mar 22 '18 at 11:07

score 0 · Answer 2 · answered Mar 22 '18 at 08:06

The SELECT INTO statement creates a table and fills it with data. Executing 2 or more times that statement will fail (since the table will be created on the first run and will try to create it again on the second one).

If the table is empty is either because the query returned no results, or the SP failed because of the reason I mentioned before (or even failed while building the results, for example with a bad conversion). When using SELECT INTO, make sure to drop the table IF EXISTS before (if this is what you want).

ALTER PROCEDURE Invoices_AGG
@year int

AS
BEGIN

    IF EXISTS( SELECT 'table exists' FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'FACT_Invoices_AGG'AND TABLE_SCHEMA = 'dbo')
        DROP TABLE dbo.FACT_Invoices_AGG

    select
        (case when MONTH(invoiceDate) >9 then concat('01','/',MONTH(invoiceDate),'/',year(invoiceDate)) else concat('01','/0',MONTH(invoiceDate),'/',year(invoiceDate)) end) as DateID,
        SUM(case when invoiceType = 'B' then invoiceTotal else 0 end) as Total_Incomes_TypeB,
        SUM(case when invoiceType = 'I' then invoiceTotal else 0 end) as Total_Incomes_TypeI
    into 
        FACT_Invoices_AGG 
    from 
        invoices
    where 
        year(invoiceDate)=@year
    group by 
        (case when MONTH(invoiceDate) >9 then concat('01','/',MONTH(invoiceDate),'/',year(invoiceDate)) else concat('01','/0',MONTH(invoiceDate),'/',year(invoiceDate)) end);

END
GO

Another option would be changing your SELECT INTO for a INSERT INTO (columnNames, ...) SELECT which requires that the table exists beforehand.

Try executing the SELECT without the INTO to see if it doesn't fail while computing the result. If it doesn't then make sure the destination table does not exist before using a SELECT INTO.

sql stored procedure, select into

2 Answers2