SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 exec stored proc. dynamically
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

arkiboys
Flowing Fount of Yak Knowledge

1417 Posts

Posted - 10/28/2013 :  04:12:30  Show Profile  Reply with Quote
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

184 Posts

Posted - 10/28/2013 :  04:45:34  Show Profile  Reply with Quote
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
Go to Top of Page

arkiboys
Flowing Fount of Yak Knowledge

1417 Posts

Posted - 10/28/2013 :  04:50:05  Show Profile  Reply with Quote
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

Edited by - arkiboys on 10/28/2013 05:51:28
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22760 Posts

Posted - 10/28/2013 :  05:54:57  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Does the procedure return multiple result sets anyway?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

arkiboys
Flowing Fount of Yak Knowledge

1417 Posts

Posted - 10/28/2013 :  11:17:38  Show Profile  Reply with Quote
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
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
381 Posts

Posted - 10/28/2013 :  17:57:36  Show Profile  Reply with Quote
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

Go to Top of Page

arkiboys
Flowing Fount of Yak Knowledge

1417 Posts

Posted - 10/29/2013 :  04:30:46  Show Profile  Reply with Quote
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.

Edited by - arkiboys on 10/29/2013 04:31:07
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22760 Posts

Posted - 10/30/2013 :  05:18:31  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

arkiboys
Flowing Fount of Yak Knowledge

1417 Posts

Posted - 10/30/2013 :  08:04:59  Show Profile  Reply with Quote
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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22760 Posts

Posted - 10/31/2013 :  02:43:17  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
See how many columns are returned by this?

exec uspMain

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

rocknpop
Posting Yak Master

184 Posts

Posted - 10/31/2013 :  02:51:19  Show Profile  Reply with Quote
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
Go to Top of Page

arkiboys
Flowing Fount of Yak Knowledge

1417 Posts

Posted - 11/04/2013 :  05:08:01  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000