exec stored proc. dynamically - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

exec stored proc. dynamically

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2013-10-28 : 04:12:30

Hi,

The following query returns five columns.

set @ExecSP = 'exec usp_Main_TEST ' + char(10) +
' @CustID = 123'

exec sp_executesql @ExecSP

Field1 Field2 Field3 Field4 Field5

---------------------------------------------
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

rocknpop
Posting Yak Master

201 Posts

Posted - 2013-10-28 : 04:45:34

Check the table and the procedure output again, somewhere the output of these two are not matching (the number of columns)

--------------------
Rock n Roll with SQL

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2013-10-28 : 04:50:05

I have checked. The output columns are exactly the same though.
I did a test by having only one column.
Even with one column I get the same error.
Is what I am doing allowed in sql?
Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-10-28 : 05:54:57

Does the procedure return multiple result sets anyway?

Madhivanan

Failing to plan is Planning to fail

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2013-10-28 : 11:17:38

quote:
Originally posted by madhivanan

Does the procedure return multiple result sets anyway?

Madhivanan

Failing to plan is Planning to fail

No, just a simple select

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-10-28 : 17:57:36

Post the proc code.

Also, verify the definition of the temp table:
EXEC tempdb.dbo.sp_help #tblMain

Finally, you can specify column names on the insert:
insert into #tblMain ( col1, col2, col3, col4, col5 )
exec sp_executesql @ExecSP

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2013-10-29 : 04:30:46

This is what I have:
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.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-10-30 : 05:18:31

look at this code

insert into tblData
exec uspSomeProc

Does the table tblData have same number of columns returned by the procedure uspSomeproc?

Madhivanan

Failing to plan is Planning to fail

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2013-10-30 : 08:04:59

quote:
Originally posted by madhivanan

look at this code

insert into tblData
exec uspSomeProc

Does the table tblData have same number of columns returned by the procedure uspSomeproc?

Madhivanan

Failing to plan is Planning to fail

Hi,
The insert into tblData happensat the early stage of the stored proc.
If you look, you see that tblData is only used inside the stored proc. and nothing from this table is returned.
You also see the three dots that I place i.e. ... which indicates there are other things happening in the stored proc.
But what is important is that the stored proc. returns the data inside #tblResult which has one column CustID
This issue I am facing is most probably because of the nested insert into table exec stored proc. is not allowed in sql 2008
but not sure how to over come this issue I am having here.
Thank you

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-10-31 : 02:43:17

See how many columns are returned by this?

exec uspMain

Madhivanan

Failing to plan is Planning to fail

rocknpop
Posting Yak Master

201 Posts

Posted - 2013-10-31 : 02:51:19

Are you not getting this error: An INSERT EXEC statement cannot be nested.

From your second sample code this is the first error you should be getting provided you are calling uspFees first.
But if you are calling uspMain first and getting the 'Column name or number' error then look into what Madhivanan has asked for. In case this is true as well then there is definitely some piece of code you have which does not match the table definition during an insert into another table.

The reason why #tblResult returns data (or for that matter your proc. executes till the end) is because you do not have any error handling in this procedure.

--------------------
Rock n Roll with SQL

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2013-11-04 : 05:08:01

quote:
Originally posted by madhivanan

See how many columns are returned by this?

exec uspMain

Madhivanan

Failing to plan is Planning to fail

One Columns which is CustID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources