how to perform insert into table from stored proc?

Hi Experts,

I have this code inside a stored procedure that inserts records into a table,

CREATE TABLE #Tmp (EmployeeID int)
EXECUTE ('INSERT INTO #Tmp ' + @Source)

Open in new window


However when the @Source is not a select statement, instead it is a stored proc returning records like the following

proc_FilterOrientationNotes122 (Param1, Param2)

I get an error message, "Incorrect syntax near 'proc_FilterOrientationNotes122'."

What is the easiest way to get this accomplished?
LVL 5
bfuchsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

arnoldCommented:
What does the stored procedure returns, a table
Assigning the response to a table variable and then querying the table variable on the insert line.
0
Dung DinhDBA and Business Intelligence DeveloperCommented:
Try it

create procedure Test
@EmployeeID int
AS
BEGIN
   SELECT 1 AS EmployeeID
END
Go
create procedure InsertTest
AS
BEGIN
   DECLARE @Source varchar(100)='EXEC dbo.Test 1'
   CREATE TABLE #Tmp (EmployeeID int)
   EXECUTE ('INSERT INTO #Tmp ' + @Source)
   SELECT * FROM #Tmp
END

GO
EXEC InsertTest

Open in new window

0
bfuchsAuthor Commented:
@arnold,

The following is the outer stored proc in question
ALTER PROCEDURE [dbo].[proc_InsertBatchNotes]
(
@Source varchar(5000),
@Note varchar(5000),
@Initial varchar(5),
@FacilityID int
)

AS

SET NOCOUNT ON

CREATE TABLE #Tmp (EmployeeID int)
EXECUTE ('INSERT INTO #Tmp ' + @Source)

SET NOCOUNT OFF

INSERT INTO NotesTbl ([Note], Initial, [Date], FacilityID, EmployeeID, MailedCard )
SELECT @Note, @Initial, CONVERT(varchar, getdate(), 101), @FacilityID, EmployeeID, 1
FROM #Tmp 

RETURN 

Open in new window


Now taking in consideration that @Source could be either a select statement or a stored proc returning records, how would you suggest I modify it?

Thanks,
Ben
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

bfuchsAuthor Commented:
@Dung Dinh,

your code prompts the following errors

Msg 139, Level 15, State 1, Procedure InsertTest, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Procedure InsertTest, Line 6
Must declare the scalar variable "@Source".
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'InsertTest'.
0
Dung DinhDBA and Business Intelligence DeveloperCommented:
I'm running it  in SQL Server 2014 Developer successfully.  Did you run full my script?
sp_InsertTest.PNG
0
arnoldCommented:
Nowhere in your example do you show what proc_FilterOrientationNotes122 creates/returns.

If it i
0
bfuchsAuthor Commented:
@Dung Dinh,
I have SQL Express 2005, do you think its not compatible?

@arnold,
Attached is how its being used & what it returns, the code is a little of a long sql, let me know if you need me to post it,

Thanks,
Ben
untitled.bmp
0
bfuchsAuthor Commented:
FYI.

below is the code of the proc_FilterOrientationNotes122

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[proc_FilterOrientationNotes122]
(@strWhere varchar(5000),
 @strRetColums varchar(500) = null

 )
AS 
declare @tmp varchar(5000)
declare @strWhere1 varchar(5000)
declare @strWhere2 varchar(5000)

set @strWhere1 = (select [value] from ProgrammingSettings where [Code] = 'UniqueFilterOrientationNotesSql')

if @strRetColums is null

	set @tmp='SELECT     TOP (99.9999) PERCENT CAST(CASE WHEN Last_Orient_Note_Day IS NULL OR
                      Last_Orient_Note_Day < GETDATE() - 60 THEN 1 ELSE 0 END AS bit) AS Red, dbo.OrientationNotes.ID, dbo.Orientations.EmployeeID, 
                      dbo.Orientations.FacilityID, dbo.Orientations.Active, dbo.OrientationNotes.Note, COALESCE (dbo.OrientationNotes.Day, dbo.Orientations.DateEntered) 
                      AS Day, dbo.OrientationNotes.Initial, dbo.Orientations.ID AS OrientationID, dbo.Orientations.DateEntered, dbo.Orientations.Initial AS OrientationInitial, 
                      dbo.Orientations.Traveler, dbo.EmployeesDocsTov.TovInfoLastDay AS LastDay, dbo.Orientations.DueDate, dbo.OrientationNotes.Mailing, 
                      dbo.Orientations.DueDateInitial, dbo.EmployeesDocsTov.Last_Orient_Note_Day, dbo.Employeestbl.AvailibilityPDays, 
                      dbo.Employeestbl.AvailibilityPShifts, dbo.Employeestbl.City, dbo.Employeestbl.LastName, dbo.Employeestbl.FirstName, dbo.Employeestbl.Title, 
                      dbo.Facilitiestbl.Name AS FacilityName, note.LastNoteID, dbo.EmployeesDocsTov.TovInfoFacility1 AS LastFacility, 
                      dbo.Employeestbl.Degree, dbo.Employeestbl.NotSendMassTextMsg, dbo.Employeestbl.NotSendMassEmail, dbo.Employeestbl.Email, 
                      dbo.Employeestbl.Beeper, dbo.Employeestbl.SMSProvider, dbo.Employeestbl.ExpectedGraduation,
					  dbo.Employeestbl.LastName + '' '' + dbo.Employeestbl.FirstName As EmpName'
if @strRetColums is not null
	set @tmp = 'Select ' + @strRetColums
set @tmp = @tmp + ' FROM         dbo.Orientations INNER JOIN
                      dbo.Employeestbl ON dbo.Orientations.EmployeeID = dbo.Employeestbl.ID INNER JOIN
                      dbo.Facilitiestbl ON dbo.Orientations.FacilityID = dbo.Facilitiestbl.ID LEFT OUTER JOIN
(SELECT     OrientationID,  MAX(o2.ID) AS LastNoteID
FROM         
dbo.OrientationNotes o2 ' + @strWhere1 + 
'  ) note ON dbo.Orientations.ID = note.OrientationID
                       LEFT OUTER JOIN
                      dbo.EmployeesDocsTov ON dbo.Orientations.EmployeeID = dbo.EmployeesDocsTov.EmployeeID LEFT OUTER JOIN
                      dbo.OrientationNotes ON dbo.Orientations.ID = dbo.OrientationNotes.OrientationID'

IF @strWhere IS not NULL and @strwhere <> 'ALL' and @strwhere <> 'Long' set @tmp = @tmp + ' where ' + @strWhere 

IF @strWhere IS NULL set @tmp = @tmp + ' where Orientations.EmployeeID = 3' 

if @strWhere = 'Long'
begin
	set @strWhere2 = (select [value] from ProgrammingSettings where [Code] = 'FilterOrientationNotesSql')
	set @tmp = @tmp + ' where ' + @strWhere2 
end

	--if charindex('order by',@strWhere)= 0 set @tmp = @tmp + ' ORDER BY Orientations.DateEntered DESC, OrientationNotes.ID desc'
	if charindex('order by',@tmp)= 0 set @tmp = @tmp + ' ORDER BY Orientations.DateEntered DESC, OrientationNotes.ID desc'
execute (@tmp)

Open in new window

0
arnoldCommented:
It returns a single column.
Why declare a temp table where a table variable will do the same thing without the need to run insert.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/5779222f-e3d2-4b99-bd66-886ce6508796/table-variable-and-dynamic-sql?forum=transactsql

Declare @tablename table (employeeid int)
Exec @tablename=proc_name arguments
Select employeeid from @tablename
0
arnoldCommented:
In your question you declare a temp table and then try to exec

Instead of your code
CREATE TABLE #Tmp (EmployeeID int)
EXECUTE ('INSERT INTO #Tmp ' + @Source)

Open in new window

Where @Source is your proc_FilterOrientationNotes122

My suggestion, declaring a table variable

Declare @tablename Table (EmployeeID int)
Exec @tablename=proc_FilterOrientationNotes122
The table returned from the proc will be accessible through @tablename

Select * @tablename will return the data from the proc
0
bfuchsAuthor Commented:
@arnold,

I see what you mean.

(I had realized you were referring to the first insert and therefore deleted my prev comment..)

Just want make sure, this will handle select statements (in @Source) as well?

Will test that tom & let you know

Thanks,
Ben
0
arnoldCommented:
IMHO, there was no need to delete you prior comments as they often help maintain the train of thought
I.e. What your thought process is and what you are after versus those commenting and what they are trying to convey/put across.
0
Dung DinhDBA and Business Intelligence DeveloperCommented:
@Dung Dinh,
I have SQL Express 2005, do you think its not compatible?

It is just EXECUTE command line, I think it works well with SQL Express 2005.
0
bfuchsAuthor Commented:
@arnold,
trying the following code
USE [PlacementNP]
GO
/****** Object:  StoredProcedure [dbo].[proc_InsertBatchNotes]    Script Date: 09/08/2015 16:32:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[proc_InsertBatchNotesTest]
(
@Source varchar(5000),
@Note varchar(5000),
@Initial varchar(5),
@FacilityID int
)
AS
Declare @tablename Table (EmployeeID int)

--SET NOCOUNT ON


Exec @tablename = @Source 

--CREATE TABLE #Tmp (EmployeeID int)
--EXECUTE ('INSERT INTO #Tmp ' + @Source)

--SET NOCOUNT OFF

INSERT INTO NotesTbl ([Note], Initial, [Date], FacilityID, EmployeeID, MailedCard )
SELECT @Note, @Initial, CONVERT(varchar, getdate(), 101), @FacilityID, EmployeeID, 1
FROM @tablename 

RETURN 

Open in new window


and getting the following error at the line containing "Exec @tablename = @Source "
Must declare the scalar variable "@tablename".
0
arnoldCommented:
What does @Source have?

Looking at the code all it has been prior to the assignment line is assigned as a varchar which is scalar ( one dimensional) while @tablename is a Table variable.
Replace @Source with your other stored procedure that will return the table of employeeid said.

There are Dynamic tsql examples to use the way you seem to be trying though you seem to be mixing
0
bfuchsAuthor Commented:
@Dung Dinh,
See attached.
untitled1.bmp
0
bfuchsAuthor Commented:
@arnold,

Replace @Source with your other stored procedure that will return the table of employeeid said.
Let me clarify what is this (InsertBatchNotes) stored proc used for and what I am trying to modify..

We have some screens in our FE application that users select a range of employees which they want to add notes, the front end calls this stored procedure with parameters like "Select EmployeeID from MyTable where...", and the procedure inserts records in the Notestbl for each of them.

Now while this works fine for select statements, when I tried use it for stored procedures returning records (one column only, EmployeeID), I got the error message original posted.

Now I am wondering how this stored procedure can be modified to accommodate both, a select statement and a stored procedure with parameters?

Thanks,
Ben
0
arnoldCommented:
Please search for variable assignment tsql

What are the contents of @Source?  Source should be defined as a table if you are passing table response contents.
In your prior example it would work if source is the query to get the data.......
The issue with what you want you want to use the response table as the basis to insert, when assigned to......


Please search for table variable assignment in tsql, there are several examples including one similar to yours that I can not post here
Where the query is built and assigned to a varchar defined variable which is then executed.
0
bfuchsAuthor Commented:
@arnold
What are the contents of @Source?

@Source is the query containing the list of EmployeesID's, like "Select EmployeeID from Scheduletbl where ScheduleDate = '1/1/15'"

Source should be defined as a table
To be honest, I am not so familiar with the t-sql language, and was not the one who created this stored proc, I am the FE programmer and try to do the minimum changes to the back end that's necessary..

I really thought with a tweak of programming it can easily be modified to handle both, however if you think its something more than that, I will create a new SP to handle inserts from stored procedure or perhaps do that work in the front end..

Thanks,
Ben
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
bfuchs, to perform what you want you'll need to add EXEC keyword before the @Source variable:
EXECUTE ('INSERT INTO #Tmp EXEC ' + @Source)

Open in new window

Now you'll need to find a way to exec the above line when is a SP or execute the original line when it's a select.
You can add a parameter to tell if you're passing a SP or not or check if the first word of @Source is a 'SELECT ' (LEFT(@Source), 7 = 'SELECT ').
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BitsqueezerCommented:
Hi Ben,

some things to mention:
if you use code from >=SQL Server 2008 you must use separate DECLARE and SET commands as the declaration with initialization in one line was introduced in SQL Server 2008. So instead of "DECLARE @Source varchar(100)='EXEC dbo.Test 1' " you must use "DECLARE @Source varchar(100); SET @Source='EXEC dbo.Test 1'".
if you need to use dynamic SQL you should always use "EXECUTE sp_executesql" instead of "EXECUTE" which is more secure.
the solution for your syntax problem is that you need to remove the round brackets from the parameter list of your stored procedure, then it should work.
Of course the complete syntax is "INSERT ... EXEC" for stored procedures (here you can't use sp_executesql)

Cheers,

Christian
0
Mark WillsTopic AdvisorCommented:
Think you are having problems with your temp table.

When you do an EXECUTE you are essentially creating another session.

So, when it returns, instead of getting access to #tmp it disappears because temp tables exist for that session.

So, if you want to pass a #tmp table around, you will need a global temp table. Just bung another hash in front of #tmp ie ##tmp

And you don't need to declare the temp table if you are performing a SELECT * INTO ##tmp FROM <source> because a "select .. into" will create the table you are selecting into. And yes I can see you are doing an INSERT INTO not a select into (changes thing a bit).

Also if your proc doesn't have an exec as part of your source, it will fail, so make sure you have the prefix "exec " inside your @source (the same way you have a select in there if it is a query string).

I will go back and re-read (plus attachments) and come up with a couple of examples... Be patient, working up a few examples as you read...
0
BitsqueezerCommented:
Hi Mark,

So, if you want to pass a #tmp table around, you will need a global temp table. Just bung another hash in front of #tmp ie ##tmp

Sorry, but that's wrong. If you run dynamic SQL you create indeed a new session, but the new session has access to all temp tables which were created before in each calling procedure before (you can also create a temp table in proc1 which calls proc2 which calls proc3 which uses a dynamic SQL and you still get access to the temp table of proc1).

Using a global temp table is in general no good idea as it would break if more than one user uses this procedure so you would need to add additional things to make it multiuser usable.

That's the reason why the code examples above creates the temp table BEFORE executing the dynamic SQL so this can use the temp table.

Cheers,

Christian
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>However when the @Source is not a select statement,
Just a thought ... Why are you not trapping this with some kind of IF statement, and then conditionally doing the @exec differently based on whether @source is a SELECT or a EXEC SP?

imo allowing both SELECT and EXEC sp_name to flow into a single dynamic SQL execution statement is a poor programming practice just begging for errors.
0
Mark WillsTopic AdvisorCommented:
OK, as promised, I have gone through the code samples and come up with the following scenarios for you to look at and ponder...

/* Problem
 CREATE TABLE #Tmp (EmployeeID int)
 EXECUTE ('INSERT INTO #Tmp ' + @Source)
 where @source could be a proc or a select
*/

-- Step 1 create a dummy / test table to play with
create table EE_My_Source_Table (ID int identity, first_name varchar(20), last_name varchar(20))

-- now populate with some test data
insert EE_My_Source_Table (first_name, last_name) values ('mark','wills'), ('ben','fuchs')

-- Step 2 create a proc so we can play
create proc EE_proc_FilterOrientationNotes (@Param1 varchar(10), @Param2 varchar(10))
as
begin 
   declare @tmp varchar(800)
   set @tmp = 'select * from EE_My_Source_Table'
   exec(@tmp)
end
go

-- now test our SP
exec EE_proc_FilterOrientationNotes 'a','b'


-- All works OK and now ready to test...


-- scenario 1 doing it long hand via select into
if object_id('tempdb..#tmp') is not null drop table #tmp
select * into #tmp from EE_My_Source_Table 

select * from #tmp
-- scenario 1 works


-- scenario 2 create #tmp table first
if object_id('tempdb..#tmp') is not null drop table #tmp
create table #tmp (id int, first_name varchar(20), last_name varchar(20))

-- scenario 2 doing it long hand via SP
insert into #tmp exec EE_proc_FilterOrientationNotes 'a','b'

select * from #tmp
-- scenario 2 works and noted that the create table is needed

-- scenario 3 - assumed availability of #tmp with select INTO
if object_id('tempdb..#tmp') is not null drop table #tmp

exec ('select * into #tmp from EE_My_Source_Table')

select * from #tmp
-- scenario 3 fails

-- scenario 4 using global temp tables ie double #
if object_id('tempdb..##tmp') is not null drop table ##tmp

exec ('select * into ##tmp from EE_My_Source_Table')

select * from ##tmp
-- scenario 4 works - but might revert to creating table first because of SP (so no select .. into)

/* 
  ok, now lets deal with the real problem at hand where @source could be select or SP
  command structure is EXECUTE ('INSERT INTO #Tmp ' + @Source)
  by creating table first, we avoid complexities of global temp tables
*/

-- scenario 5

if object_id('tempdb..#tmp') is not null drop table #tmp
create table #tmp (id int, first_name varchar(20), last_name varchar(20))

declare @source varchar(800)
set @source = 'select * from EE_My_Source_Table'
EXECUTE ('INSERT INTO #Tmp ' + @Source)
select * from #tmp
-- scenario 5 works

-- scenario 6

if object_id('tempdb..#tmp') is not null drop table #tmp
create table #tmp (id int, first_name varchar(20), last_name varchar(20))

declare @source varchar(800)
set @source = 'exec EE_proc_FilterOrientationNotes ''a'',''b'''
EXECUTE ('INSERT INTO #Tmp ' + @Source)
select * from #tmp

-- scenario 6 works note the EXEC is part of @source much the same way SELECT is there for scenario 5

Open in new window

0
Mark WillsTopic AdvisorCommented:
Crikey, been a lot of activity...

@bitsqueezer,

No, not wrong because I was basing that comment on a "SELECT INTO" so if the table doesn't exist, then it is the spawned process that owns the table and does not pass back to parent.

But having read through all the comments and attachments, decided that "SELECT INTO" was not the correct process. The SP wasn't returning a table, just executing its own dynamic SQL. I should have taken the time in the first place... Assumption being something was happening in the SP.

Otherwise you are correct. See scenario 3 and scenario 4 in my post above for clarification.

The two things I can think of (without replicating the SP, is the absence of EXEC or a mine field of quotes being misaligned.

Shown in my example as scenario 6.
0
ZberteocCommented:
The correct syntax for insert is:

From select:
INSERT INTO tbl (<col_list>) SELECT <col_list> FROM...

From stored procedure
INSERT INTO tbl (<col_list>) EXEC procedure

Where you have to make sure that the procedure returns a result set having the same <col_list> like in the insert.

Considering that in the @source variable you have either a select statement or a stored procedure name In your code would do something like this:
DECLARE
    @sql nvarchar(max)='INSERT INTO #Tmp '+CASE WHEN @Source LIKE 'SELECT %' THEN @Source ELSE 'EXEC '+@Source END	 
CREATE TABLE #Tmp (EmployeeID int)
EXECUTE (@sql)

Open in new window

0
bfuchsAuthor Commented:
Hi Experts,

I guess you all got it right
The exec solution worked.

Thank You!

@Zberteoc,
Special thanks for helping out with the exact syntax.

Ben
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.