Insert Into Select stored procedure not working inserting NULL

Question

My first time creating a stored procedure here please go easy on me. I was trying to SELECT my data from Table 1 (EmpTBL), then INSERT it into Table 2 (EventTBL)

I think the culprit is this line right here:

@Ename varchar(250) = NULL, 
@Edate varchar(250) = NULL,

I think my logic is in the right direction I just dont why it doesn't work.

Not sure what to do next.

ALTER PROCEDURE spBdayEmp
   (@Ename varchar(250) = NUll,
    @Edate varchar(250)= NUll,
    @Etype varchar(250)  = 'Bday')
AS 
    INSERT INTO EventTBL(EventName, EventDate, EventType)
    VALUES (@Ename, @Edate, @Etype)

    SELECT
        @Ename =  (Ename + ' ' + Lname),    
        @Edate =  DATEADD(YY, DATEPART(YYYY, GETDATE()) - DATEPART(YYYY,dateOfBirth), dateOfBirth) 
    FROM 
        EmpTBL   
    WHERE  
        DATEADD( Year, DATEPART( Year, GETDATE()) - DATEPART( Year, DateOfBirth), DateOfBirth) BETWEEN CONVERT( DATE, GETDATE()) AND CONVERT( DATE, GETDATE() + 30);

how would i get the values for? @Ename, @Edate?

i would like the result of the SELECT query "Ename + ' ' + Lname," equals to "@Ename" and " DATEADD(YY, DATEPART(YYYY, GETDATE()) - DATEPART(YYYY,dateOfBirth), dateOfBirth)," equals to "@Edate"?

Which RDBMS is this for? Please add a tag to specify whether you're using `mysql`, `postgresql`, `sql-server`, `oracle` or `db2` - or something else entirely. — marc_s, Oct 29 '15 at 08:55
And also: how do you **call** this stored procedure? What **values** are you passing in to the parameters? — marc_s, Oct 29 '15 at 08:55
i am just testing it first in the sql server management studio to see if it works before i call it in my asp.net VB project — Terrence McGinnis, Oct 29 '15 at 09:03

score 0 · Answer 1 · edited Oct 29 '15 at 09:03

0

You do your insert before you have selected values into your variables.

And anothet consideration - how will your code handle returning 2 or more results from your select?

You may be better using a pattern like;

Insert into table2 select values from table1

edited Oct 29 '15 at 09:03

MusicLovingIndianGirl

5,909
9
34
65

answered Oct 29 '15 at 08:57

Karl

3,312
21
27

score 0 · Answer 2 · answered Oct 29 '15 at 09:10

i hope you have only 3 columns in EventTBL table. please update your query as below:

please change your query as below and then try

ALTER PROCEDURE spBdayEmp
AS 
INSERT INTO EventTBL(EventName, EventDate, EventType)
SELECT
    @Ename =  (Ename + ' ' + Lname),    
    @Edate =  DATEADD(YY, DATEPART(YYYY, GETDATE()) - DATEPART(YYYY,dateOfBirth), dateOfBirth) ,'Bday'
FROM 
    EmpTBL   
WHERE  
    DATEADD( Year, DATEPART( Year, GETDATE()) - DATEPART( Year, DateOfBirth), DateOfBirth) BETWEEN CONVERT( DATE, GETDATE()) AND CONVERT( DATE, GETDATE() + 30);

im getting this error mr. singh "Must declare the scalar variable "@Ename"." — Terrence McGinnis, Nov 02 '15 at 02:48

score 0 · Answer 3 · answered Oct 29 '15 at 09:10

0

Try it like this -

ALTER PROCEDURE spBdayEmp
       (@Etype varchar(250)  = 'Bday')
    AS 
        INSERT INTO EventTBL(EventName, EventDate, EventType)
        SELECT
            Ename + ' ' + Lname,    
            DATEADD(YY, DATEPART(YYYY, GETDATE()) - DATEPART(YYYY,dateOfBirth), dateOfBirth),
            @Etype 
        FROM 
            EmpTBL   
        WHERE  
            DATEADD( Year, DATEPART( Year, GETDATE()) - DATEPART( Year, DateOfBirth), DateOfBirth) BETWEEN CONVERT( DATE, GETDATE()) AND CONVERT( DATE, GETDATE() + 30);

Note: I see you have used @Etype parameter to the SP and assigned some default values to it. When executing the SP You can't pass @Etypeif you want the default to get used.

answered Oct 29 '15 at 09:10

Abhishek

2,482
1
21
32

i intentionaly set tthe @Etype value as 'Bday' as a Default – Terrence McGinnis Oct 29 '15 at 09:59
how would i get the values for? @Ename, @Edate? i would like the result of this " Ename + ' ' + Lname," equals to "@Ename" and " DATEADD(YY, DATEPART(YYYY, GETDATE()) - DATEPART(YYYY,dateOfBirth), dateOfBirth)," equals to "@Edate"? – Terrence McGinnis Nov 02 '15 at 02:45
To get the values you can use the below in `SELECT` statement - `@Ename = (Ename + ' ' + Lname) @Edate = DATEADD(YY, DATEPART(YYYY, GETDATE()) - DATEPART(YYYY,dateOfBirth), dateOfBirth)` But remember to use these values for `INSERT`, you need to use the `INSERT` after the `SELECT` is done. – Abhishek Nov 02 '15 at 05:39

score 0 · Accepted Answer · edited May 23 '17 at 12:03

after reading INSERT statement cannot contain a SELECT statement -sql server2012

i realized i had it all wrong so after some experimenting i finally found a solution.

ALTER PROCEDURE spBdayEmp
AS INSERT INTO 
EventTBL SELECT  (Ename + ' ' + Lname), DATEADD(YY, DATEPART(YYYY, GETDATE()) - DATEPART(YYYY,dateOfBirth), dateOfBirth),('Bday')FROM 
EmpTBL WHERE DATEADD( Year, DATEPART( Year, GETDATE()) - DATEPART( Year, DateOfBirth), DateOfBirth) BETWEEN CONVERT( DATE, GETDATE()) AND CONVERT( DATE, GETDATE() + 30);

Thanks for everyone who's been trying to help out!

Insert Into Select stored procedure not working inserting NULL

4 Answers4