Post reply

insert into table execution of a stored procedure

  • 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

    id Mid ValueMid CatParent Enabled LastUpdate company

    1 20 100001 NULL 25 NULL NULL

    2 20 100007 NULL 25 NULL NULL

    3 20 100030 NULL 25 NULL NULL

    4 20 100042 NULL 25 NULL NULL

    5 20 100043 NULL 25 NULL NULL

    BUT I see this error

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

    I don't know what is wrong.

  • November 30, 2013 at 12:43 am

    #1669896

    SQL Server Version?

  • November 30, 2013 at 12:50 am

    #1669897

    sql server 2008 R2

  • November 30, 2013 at 12:56 am

    #1669899

    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)
    Intro to Tally Tables and Functions

  • November 30, 2013 at 1:04 am

    #1669900

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

  • November 30, 2013 at 1:17 am

    #1669904

    Yes they are same

  • November 30, 2013 at 7:31 am

    #1669911

    can you please provide definition of your sql procedure

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

  • December 2, 2013 at 1:16 pm

    #1670220

    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)

  • December 3, 2013 at 12:51 pm

    #1670516

    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

  • December 4, 2013 at 4:12 pm

    #1670886

    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)
    Intro to Tally Tables and Functions

  • December 4, 2013 at 4:13 pm

    #1670887

    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)
    Intro to Tally Tables and Functions

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

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