March 24, 2009 at 12:12 pm
When I try to insert the result set of the following stored proceudure into a temp table I am getting the followin error.
An INSERT EXEC statement cannot be nested.
HOw can I achieve this?
Right now I have a stored procedure with this type of code where I have couple of dynamic sql statements and the result of the those two sql statements will be inserted into two temp tables. I can not change that code. All I have to do is I have to copy the result set of the procedure into another temp table.
@city1 varchar(50),@city2 varchar(50)
AS
SET NOCOUNT ON
DECLARE @sqlstmt1 varcahr(1000),@sqlstmt1 varcahr(1000)
CREATE TABLE #temp1(number int,name varchar(100))
CREATE TABLE #temp2(number int,name varchar(100))
SELECT @sqlstmt1='SELECT number,name FROM tbl1 WHERE city='+@city1
SELECT @sqlstmt2='SELECT number,name FROM tbl1 WHERE city='+@city2
INSERT INTO #temp1
EXEC(@sqlstmt1)
INSERT INTO #temp2
EXEC(@sqlstmt2)
SELECT * FROM #temp1
UNION
SELECT * FROM #temp2
GO
CREATE TABLE #temp3 (number int,name varchar(100))
EXEC proc1 'lodon','paris'
Thanks.
March 24, 2009 at 12:48 pm
Hi
If your sample fits to your business requirement just avoid the nested INSERT-EXECUTE:
USE tempdb
GO
IF (OBJECT_ID('tbl1') IS NOT NULL)
DROP TABLE tbl1
GO
CREATE TABLE tbl1 (id INT, txt VARCHAR(100))
GO
INSERT INTO tbl1
SELECT 1, 'bla'
UNION SELECT 2, 'bluff'
GO
IF (OBJECT_ID('usp_test') IS NOT NULL)
DROP PROCEDURE usp_test
GO
CREATE PROCEDURE usp_test
@id1 INT,
@id2 INT
AS
DECLARE @sql NVARCHAR(MAX)
SET @sql = 'SELECT * FROM tbl1 WHERE id = ' + CONVERT(VARCHAR(10), @id1) +
' UNION SELECT * FROM tbl1 WHERE id = ' + CONVERT(VARCHAR(10), @id2)
EXECUTE sp_executesql @sql
GO
DECLARE @result TABLE (id INT, txt VARCHAR(100))
INSERT INTO @result
EXECUTE usp_test 1, 2
SELECT * FROM @result
Greets
Flo
March 24, 2009 at 1:09 pm
Thanks for the help. It worked.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply