insert into table execution of a stored procedure

Question

Post reply

insert into table execution of a stored procedure

bkshn

Right there with Babe

Points: 794
More actions
November 30, 2013 at 12:19 am

#279563

Hello
I have this table
declare @UserMidListTable Table (Id Int,Mid int,ValueMid int,CatParent int, [Enabled] int,LastUpdate Datetime,Company nvarchar(max))
I want to fill it with the execution of this stored procedure
insert into @UserMidListTable (Id ,Mid ,ValueMid ,CatParent , [Enabled] ,LastUpdate ,Company)
exec UserMidList @userRkey,'20','0','0','10','1','2','',''
the execution of stored procedure is bellow
idMidValueMidCatParentEnabledLastUpdatecompany
120100001NULL25NULLNULL
220100007NULL25NULLNULL
320100030NULL25NULLNULL
420100042NULL25NULLNULL
520100043NULL25NULLNULL
BUT I see this error
Column name or number of supplied values does not match table definition.
I don't know what is wrong.

Viewing 11 posts - 1 through 10 (of 10 total)

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

samulasudharshan Grasshopper Points: 14 More actions · Answer 1

samulasudharshan

Grasshopper

Points: 14

November 30, 2013 at 12:43 am

#1669896

SQL Server Version?

bkshn Right there with Babe Points: 794 More actions · Answer 2

bkshn

Right there with Babe

Points: 794

November 30, 2013 at 12:50 am

#1669897

sql server 2008 R2

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 3

Is your server case sensitive by any chance? Also, what is the compatibility level for the database set to?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

samulasudharshan Grasshopper Points: 14 More actions · Answer 4

Are the datatypes of the output columns from stored procedure same as the Table Variable?

bkshn Right there with Babe Points: 794 More actions · Answer 5

bkshn

Right there with Babe

Points: 794

November 30, 2013 at 1:17 am

#1669904

Yes they are same

kapil_kk SSC-Insane Points: 21316 More actions · Answer 6

can you please provide definition of your sql procedure

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

harsha.majety SSCommitted Points: 1537 More actions · Answer 7

I ran following on sql server 2008 r2 with no errors . you prbly need to check your sproc definition.

if exists(select * from sys.objects (nolock) where name = 'UserMidList' and type = 'P')

begin

exec('drop proc UserMidList');

end

go

create proc UserMidList as

select 1,20,100001,NULL,25,NULL,NULL

union

select 2,20,100007,NULL,25,NULL,NULL

union

select 3,20,100030,NULL,25,NULL,NULL

union

select 4,20, 100042,NULL,25,NULL,NULL

union

select 5,20,100043,NULL,25,NULL,NULL

go

declare @UserMidListTable Table (Id Int,Mid int,ValueMid int,CatParent int, [Enabled] int,LastUpdate Datetime,Company nvarchar(max))

insert into @UserMidListTable (Id ,Mid ,ValueMid ,CatParent , [Enabled] ,LastUpdate ,Company)

exec UserMidList

select * from @UserMidListTable

go

select @@version

-- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)

--Jun 17 2011 00:54:03

--Copyright (c) Microsoft Corporation

--Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

Andy Steinke Mr or Mrs. 500 Points: 565 More actions · Answer 8

You could use OPENROWSET to create a temporary table (example and link to example web page below) and check that the stored procedure definition matches your table variable definition.

SELECT * into #Temp2

FROM OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;','set fmtonly off exec master.dbo.sp_who2')

From http://blogs.technet.com/b/wardpond/archive/2005/08/01/the-openrowset-trick-accessing-stored-procedure-output-in-a-select-statement.aspx

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 9

The problem with using OPENROWSET is that it requires "SA" privs. That's ok for jobs but should not be ok for application logins.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 10

bkshn (11/30/2013)
Yes they are same

As I asked before, have you checked the compatability level of the database?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)