4

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

Improve this question
7
  • getUserProfile(@userID) code?? Commented Aug 12, 2015 at 5:01
  • Didn't find error in this code... I think error in second procedure - exec getUserProfile(@userID) Commented Aug 12, 2015 at 5:02
  • you are missing keyword 'values' in insert statement. see my answer. Commented Aug 12, 2015 at 5: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. Commented Aug 12, 2015 at 5:26
  • @Edison - Still i would say its a duplicate. People can give you idea of solving your problem not exact answer. Commented Aug 12, 2015 at 5:35

3 Answers 3

Reset to default
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
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

two table variables - Would not that be a bad design ?
@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.
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);
Improve this answer

1 Comment

nope tht does not work.. :( I am still getting the same error..
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

Improve this answer

6 Comments

if i do that I get " Incorrect syntax near the keyword 'EXEC' " erorr.
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.
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..
Yes, that works. Because, in that case, you are not treating procedure as paramter. It is executed as standalone statement.
but that would require me changing the getUserProfile(@userID) procedure right ? Unfortunately I cannot do that 'cause it is already in production!!!
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.