SQL Server insertion based on stored procedure execution results

Question

I have a table variable with two columns. The first column is the value that I am trying to populate while the second value is populated by executing the 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 to execute a stored procedure:

Level 15, State 1, UserProfiles Procedure, Line 9
[Microsoft] [ODBC SQL Server Driver] [SQL Server] Invalid syntax next to "userprofile".

Any help would be well appreciated.

Thanks in advance

Answer 1

Your SP probably has a select statement,

see SQL Server - SELECTING from a 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 

Answer 2

My guess: you need to end your statement with a semicolon ;

DECLARE @local_variable (Transact-SQL)

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

Answer 3

Missing values. Also, consider using the out parameter to get the value from the 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 

In addition, you cannot make a built-in procedure call. use parameter.

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