exec stored proc. dynamically

Question

Post reply

exec stored proc. dynamically

arkiboys

Hall of Fame

Points: 3958
More actions
October 28, 2013 at 2:13 am

#299662

Hi,
The following query returns five columns.
set @ExecSP = 'exec usp_Main_TEST ' + char(10) +
' @CustID = 123'
exec sp_executesql @ExecSP
Field1Field2Field3Field4Field5
---------------------------------------------
Running the following query which puts the result of the stored proc. into a temp table gives the following error:
Column name or number of supplied values does not match table definition.
create table #tblMain
(
Field1 int null,
Field2 int null,
Field3 int null,
Field4 int null,
Field5 int null
)
set @ExecSP = 'exec usp_Main_TEST ' + char(10) +
' @CustID = 123'
insert into #tblMain
exec sp_executesql @ExecSP
Do you know what I am doing wrong please?
Thanks

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

What dioes this returns exec usp_Main_TEST @CustID = '123'

Actually your no. of columns return from Sp are nto matching with temp table coluimns count ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

arkiboys Hall of Fame Points: 3958 More actions · Answer 2

Yes, I can see that in the message but I have checked several times and the temp table has the exact column counts and names as the result of the sp.

Even if only one column is returned and the temp table has that column only, I still get the same message.

Any thoughts please?

Thanks

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 3

try this

insert into #tblmain

exec usp_Main_TEST @CustID = '123'

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

arkiboys Hall of Fame Points: 3958 More actions · Answer 4

The stored proc has in it a few queries with insert into...

So if I run what you suggested, I get the error:

INSERT EXEC statement cannot be nested

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

arkiboys (10/28/2013)
The stored proc has in it a few queries with insert into...
So if I run what you suggested, I get the error:
INSERT EXEC statement cannot be nested

I think now is the time you need to provide us with some details. What does the actual proc code look like? Why are doing this with dynamic sql? Let's start there and see what other information we may need in order to help.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

arkiboys Hall of Fame Points: 3958 More actions · Answer 6

Initially, I get the nested error...

So then I tried to see if I can over come the error by running dynamic sql to execute the sp and insert into a temp table. But this gives the error I mentioned earlier i.e. column names do not match...

Sean Lange SSC Guru Points: 286590 More actions · Answer 7

arkiboys (10/28/2013)
Initially, I get the nested error...
So then I tried to see if I can over come the error by running dynamic sql to execute the sp and insert into a temp table. But this gives the error I mentioned earlier i.e. column names do not match...

This is not providing the details required for others to help you. What is the code for the proc? What is the code you are running?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

arkiboys Hall of Fame Points: 3958 More actions · Answer 8

Hello,

I have simplified the stored procs as follows.

Note that the uspMain has an insert into query

create procedure uspMain

as

insert into tblData

exec uspSomeProc

...

create table #tblResult(CustID int null)

insert into #tblResult(CustID) values(360)

select * from #tblResult

return

---------------

create procedure uspFees

as

create table #tblData(CustID int null)

insert into #tblData(CustID)

exec uspMain

--error is:

--Column name or number of supplied values does not match table definition.

Sean Lange SSC Guru Points: 286590 More actions · Answer 9

What is returned from the following?

exec uspMain

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

arkiboys Hall of Fame Points: 3958 More actions · Answer 10

arkiboys

Hall of Fame

Points: 3958

October 28, 2013 at 10:23 am

#1662088

CustID

-------

360

arkiboys Hall of Fame Points: 3958 More actions · Answer 11

Sean Lange (10/28/2013)
What is returned from the following?
exec uspMain

CustID

-------

360

arkiboys Hall of Fame Points: 3958 More actions · Answer 12

This error message is entirely mis-leading.

You are not allowed to have nested procedures containing INSERT INTO EXEC

Not sure how to solve it though.

Sean Lange SSC Guru Points: 286590 More actions · Answer 13

arkiboys (10/28/2013)
This error message is entirely mis-leading.
You are not allowed to have nested procedures containing INSERT INTO EXEC
Not sure how to solve it though.

Finally an error message.

It is really hard to figure out what is going on here because you are not posting all the details.

The error message is pretty explicit in what the problem is. You cannot nest insert into exec statements. Why do you need the temp table inside uspMain? That table is not visible outside the proc and from what you posted there is no need for it.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

arkiboys Hall of Fame Points: 3958 More actions · Answer 14

Sean Lange (10/28/2013)
arkiboys (10/28/2013)
This error message is entirely mis-leading.
You are not allowed to have nested procedures containing INSERT INTO EXEC
Not sure how to solve it though.
Finally an error message.
It is really hard to figure out what is going on here because you are not posting all the details.
The error message is pretty explicit in what the problem is. You cannot nest insert into exec statements. Why do you need the temp table inside uspMain? That table is not visible outside the proc and from what you posted there is no need for it.

I have simplified the stored procedures quite a lot.

IN each stored proc. there there is a lot going on...

The temp tables you are referring to can not be altered due to business requirements and existing codes, etc.

Thanks