SQL Server insert based on the execution results of a stored procedure

Question

I have a table variable with two columns. The first column is a value that I am trying to populate while the second value is being populated by the execution of a stored procedure.

CREATE PROCEDURE [dbo].userProfiles
   (@userID INT) AS
BEGIN
    DECLARE @sampleTable TABLE (moduleName VARCHAR(20),
                                userProfile int)

    INSERT INTO @sampleTable('userprofile', exec getUserProfile(@userID))

    SELECT *
        FROM @sampleTable
END

However, I keep getting this error whenever I try executing the stored procedure:

Level 15, State 1, Procedure userProfiles, Line 9
[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'userprofile'.

Any help will be well appreciated .

Thanks in advance

Didn't find error in this code... I think error in second procedure - exec getUserProfile(@userID) — Puneet Chawla, Aug 12 '15 at 05:02
you are missing keyword 'values' in insert statement. see my answer. — Sateesh Pagolu, Aug 12 '15 at 05:04
@indian this is not a duplicate - i would think a temporary table is different from a table variable... Also the question is a bit different that mine. my question i am using a column with the results from a SP - which is not what the other question had. — Edison, Aug 12 '15 at 05:26
@Edison - Still i would say its a duplicate. People can give you idea of solving your problem not exact answer. — Pரதீப், Aug 12 '15 at 05:35
@indian Isn't that the case with every answer here on SO. At the end of the days it's me aka the person who asked a question who will have to resolve my (his/her) issues.. Awesome Guys and Gals here just help people like me .. just saying... — Edison, Aug 12 '15 at 05:41
@Edison - I believe Praveen's answer should solve your problem. Btw two table variable is not a bad design — Pரதீப், Aug 12 '15 at 05:50

score 2 · Answer 1 · edited May 23 '17 at 12:23

2

Probably your SP do have a select statement,

see: SQL Server - SELECT FROM stored procedure

CREATE PROCEDURE [dbo].userProfiles(
    @userID INT
) AS
BEGIN
    DECLARE @sampleTable TABLE (
        moduleName VARCHAR(20),
        userProfile int
    )

    DECLARE @stored_proc_table TABLE (
        clmn datatype --similar as result set from getUserProfile
    )

    insert into @stored_proc_table exec getUserProfile(@userID)


    INSERT INTO @sampleTable 
    select 'userprofile', clmn from @stored_proc_table;

    SELECT * FROM @sampleTable
END

edited May 23 '17 at 12:23

Community

1
1

answered Aug 12 '15 at 05:06

Praveen

8,945
4
31
49

1

two table variables - Would not that be a bad design ? – Edison Aug 12 '15 at 05:14
@Edison that is the way you can `select` from a stored proc. or change the `sp` to a `table value function`. now you can deal with a one `table variable`. – Praveen Aug 12 '15 at 05:48

score 0 · Answer 2 · edited Aug 12 '15 at 05:14

0

My guess is you need end your statement with semicolon ;

DECLARE @local_variable (Transact-SQL)

DECLARE @MyTableVar table(
    EmpID int NOT NULL,
    OldVacationHours int,
    NewVacationHours int,
    ModifiedDate datetime);

edited Aug 12 '15 at 05:14

marc_s

732,580
175
1,330
1,459

answered Aug 12 '15 at 04:59

Juan Carlos Oropeza

47,252
12
78
118

nope tht does not work.. :( I am still getting the same error.. – Edison Aug 12 '15 at 05:02

Sateesh Pagolu · Answer 3 · 2015-08-12T05:30:07.227

0

Missing values. Also, consider using out parameter to get value from procedure.

CREATE PROCEDURE [dbo].userProfiles(
    @userID INT) AS
    BEGIN
        DECLARE @sampleTable TABLE(
            moduleName VARCHAR(20),
            userProfile int)

       Create table #valueholder(resultvalue int)
        insert into #valueholder exec getUserProfile(@userID)
        INSERT
          INTO @sampleTable 
select 'userprofile',resultvalue  from #valueholder

        SELECT *
          FROM @sampleTable
    END

Also, you cannot make an inline call to procedure. use out parameter.

here is an example about out param. https://technet.microsoft.com/en-us/library/ms187004(v=sql.105).aspx

edited Aug 12 '15 at 05:30

answered Aug 12 '15 at 05:04

Sateesh Pagolu

9,282
2
30
48

if i do that I get " Incorrect syntax near the keyword 'EXEC' " erorr. – Edison Aug 12 '15 at 05:06
As mentioned in the answer, use out parameter to get value from procedure.you cannot call procedure from insert statement. If possible, use function instead of procedure. – Sateesh Pagolu Aug 12 '15 at 05:08
if I remove the modulename from the temp table and the from the insert statement then it works.. it does not work when i pass the modulename. everything else is the same.. – Edison Aug 12 '15 at 05:11
Yes, that works. Because, in that case, you are not treating procedure as paramter. It is executed as standalone statement. – Sateesh Pagolu Aug 12 '15 at 05:16
but that would require me changing the getUserProfile(@userID) procedure right ? Unfortunately I cannot do that 'cause it is already in production!!! – Edison Aug 12 '15 at 05:20
@Edison : i updated my answer with a workaround. – Sateesh Pagolu Aug 12 '15 at 05:30

SQL Server insert based on the execution results of a stored procedure

3 Answers3