解决存储过程返回多结果集无法用INSERT EXEC问题

--测试环境：SQL Server 2008

 

EXEC testdb..sp_depends t_icitem

/*

--结果集:

name   type   updated    selected   column

dbo.t_ICItemMaterial user table no  no  FItemID

dbo.t_ICItemMaterial user table no  no  FOrderRector

dbo.t_ICItemMaterial user table no  no  FPOHghPrcMnyType

dbo.t_ICItemMaterial user table no  no  FPOHighPrice

dbo.t_ICItemMaterial user table no  no  FWWHghPrc

dbo.t_ICItemMaterial user table no  no  FWWHghPrcMnyType

dbo.t_ICItemMaterial user table no  no  FClass

dbo.t_ICItemMaterial user table no  no  FCostDiffRate

dbo.t_ICItemMaterial user table no  no  FDepartment

dbo.t_ICItemMaterial user table no  no  FSaleTaxAcctID

dbo.t_ICItemMaterial user table no  no  FCBBmStandardID

dbo.t_ICItemMaterial user table no  no  FCBRestore

dbo.t_ICItemMaterial user table no  no  FLastCheckDate

dbo.t_ICItemMaterial user table no  no  FCheckCycle

dbo.t_ICItemMaterial user table no  no  FCheckCycUnit

dbo.t_ICItemMaterial user table no  no  FStockPrice

dbo.t_ICItemMaterial user table no  no  FABCCls

dbo.t_ICItemMaterial user table no  no  FBatchQty

dbo.t_ICItemMaterial user table no  no  FIsSpecialTax

dbo.t_ICItemMaterial user table no  no  FSOHighLimit

dbo.t_ICItemMaterial user table no  no  FSOLowLimit

dbo.t_ICItemMaterial user table no  no  FOIHighLimit

dbo.t_ICItemMaterial user table no  no  FOILowLimit

dbo.t_ICItemMaterial user table no  no  FDaysPer

dbo.t_ICItemMaterial user table no  no  FStockTime

dbo.t_ICItemMaterial user table no  no  FBookPlan

dbo.t_ICItemMaterial user table no  no  FBeforeExpire

dbo.t_ICItemMaterial user table no  no  FTaxRate

dbo.t_ICItemMaterial user table no  no  FAdminAcctID

dbo.t_ICItemMaterial user table no  no  FNote

dbo.t_ICItemMaterial user table no  no  FSaleAcctID

dbo.t_ICItemMaterial user table no  no  FCostAcctID

dbo.t_ICItemMaterial user table no  no  FAPAcctID

dbo.t_ICItemMaterial user table no  no  FGoodSpec

dbo.t_ICItemMaterial user table no  no  FCostProject

dbo.t_ICItemMaterial user table no  no  FIsSnManage

dbo.t_ICItemMaterial user table no  no  FISKFPeriod

dbo.t_ICItemMaterial user table no  no  FKFPeriod

dbo.t_ICItemMaterial user table no  no  FTrack

dbo.t_ICItemMaterial user table no  no  FPlanPrice

dbo.t_ICItemMaterial user table no  no  FPriceDecimal

dbo.t_ICItemMaterial user table no  no  FAcctID

dbo.t_ICItemMaterial user table no  no  FSOLowPrc

dbo.t_ICItemMaterial user table no  no  FSOLowPrcMnyType

dbo.t_ICItemMaterial user table no  no  FIsSale

dbo.t_ICItemMaterial user table no  no  FProfitRate

dbo.t_ICItemMaterial user table no  no  FSalePrice

dbo.t_ICItemMaterial user table no  no  FBatchManager

dbo.t_ICItemPlan  user table no  no  FItemID

dbo.t_ICItemPlan  user table no  no  FPlanTrategy

dbo.t_ICItemPlan  user table no  no  FOrderTrategy

dbo.t_ICItemPlan  user table no  no  FLeadTime

dbo.t_ICItemPlan  user table no  no  FFixLeadTime

dbo.t_ICItemPlan  user table no  no  FTotalTQQ

dbo.t_ICItemPlan  user table no  no  FBackFlushSPID

dbo.t_ICItemPlan  user table no  no  FCtrlType

dbo.t_ICItemPlan  user table no  no  FCtrlStraregy

dbo.t_ICItemPlan  user table no  no  FContainerName

dbo.t_ICItemPlan  user table no  no  FKanBanCapability

dbo.t_ICItemPlan  user table no  no  FIsBackFlush

dbo.t_ICItemPlan  user table no  no  FBackFlushStockID

dbo.t_ICItemPlan  user table no  no  FInLowLimit

dbo.t_ICItemPlan  user table no  no  FLowestBomCode

dbo.t_ICItemPlan  user table no  no  FMRPOrder

dbo.t_ICItemPlan  user table no  no  FIsCharSourceItem

dbo.t_ICItemPlan  user table no  no  FCharSourceItemID

dbo.t_ICItemPlan  user table no  no  FPlanMode

dbo.t_ICItemPlan  user table no  no  FProductPrincipal

dbo.t_ICItemPlan  user table no  no  FDailyConsume

dbo.t_ICItemPlan  user table no  no  FMRPCon

dbo.t_ICItemPlan  user table no  no  FPlanner

dbo.t_ICItemPlan  user table no  no  FPutInteger

dbo.t_ICItemPlan  user table no  no  FInHighLimit

dbo.t_ICItemPlan  user table no  no  FBatFixEconomy

dbo.t_ICItemPlan  user table no  no  FBatChangeEconomy

dbo.t_ICItemPlan  user table no  no  FRequirePoint

dbo.t_ICItemPlan  user table no  no  FPlanPoint

dbo.t_ICItemPlan  user table no  no  FDefaultRoutingID

dbo.t_ICItemPlan  user table no  no  FDefaultWorkTypeID

dbo.t_ICItemPlan  user table no  no  FQtyMin

dbo.t_ICItemPlan  user table no  no  FQtyMax

dbo.t_ICItemPlan  user table no  no  FCUUnitID

dbo.t_ICItemPlan  user table no  no  FOrderInterVal

dbo.t_ICItemPlan  user table no  no  FBatchAppendQty

dbo.t_ICItemPlan  user table no  no  FOrderPoint

dbo.t_ICItemDesign   user table no  no  FItemID

dbo.t_ICItemDesign   user table no  no  FChartNumber

dbo.t_ICItemDesign   user table no  no  FIsKeyItem

dbo.t_ICItemDesign   user table no  no  FMaund

dbo.t_ICItemDesign   user table no  no  FGrossWeight

dbo.t_ICItemDesign   user table no  no  FNetWeight

dbo.t_ICItemDesign   user table no  no  FCubicMeasure

dbo.t_ICItemDesign   user table no  no  FLength

dbo.t_ICItemDesign   user table no  no  FWidth

dbo.t_ICItemDesign   user table no  no  FHeight

dbo.t_ICItemDesign   user table no  no  FSize

dbo.t_ICItemDesign   user table no  no  FVersion

dbo.T_BASE_ICItemEntrance   user table no  no  FItemID

dbo.T_BASE_ICItemEntrance   user table no  no  FNameEn

dbo.T_BASE_ICItemEntrance   user table no  no  FModelEn

dbo.T_BASE_ICItemEntrance   user table no  no  FHSNumber

dbo.T_BASE_ICItemEntrance   user table no  no  FFirstUnit

dbo.T_BASE_ICItemEntrance   user table no  no  FSecondUnit

dbo.T_BASE_ICItemEntrance   user table no  no  FWeightDecimal

dbo.T_BASE_ICItemEntrance   user table no  no  FImpostTaxRate

dbo.T_BASE_ICItemEntrance   user table no  no  FConsumeTaxRate

dbo.T_BASE_ICItemEntrance   user table no  no  FManageType

dbo.T_BASE_ICItemEntrance   user table no  no  FExportRate

dbo.T_BASE_ICItemEntrance   user table no  no  FFirstUnitRate

dbo.T_BASE_ICItemEntrance   user table no  no  FSecondUnitRate

dbo.T_BASE_ICItemEntrance   user table no  no  FIsManage

dbo.T_BASE_ICItemEntrance   user table no  no  FPackType

dbo.T_BASE_ICItemEntrance   user table no  no  FLenDecimal

dbo.T_BASE_ICItemEntrance   user table no  no  FCubageDecimal

dbo.t_ICItemStandard user table no  no  FItemID

dbo.t_ICItemStandard user table no  no  FStandardCost

dbo.t_ICItemStandard user table no  no  FStandardManHour

dbo.t_ICItemStandard user table no  no  FStdPayRate

dbo.t_ICItemStandard user table no  no  FChgFeeRate

dbo.t_ICItemStandard user table no  no  FStdFixFeeRate

dbo.t_ICItemStandard user table no  no  FCBRouting

dbo.t_ICItemStandard user table no  no  FPCVAcctID

dbo.t_ICItemStandard user table no  no  FSLAcctID

dbo.t_ICItemStandard user table no  no  FCAVAcctID

dbo.t_ICItemStandard user table no  no  FCBAppendRate

dbo.t_ICItemStandard user table no  no  FCBAppendProject

dbo.t_ICItemStandard user table no  no  FCostBomID

dbo.t_ICItemStandard user table no  no  FOutMachFee

dbo.t_ICItemStandard user table no  no  FPieceRate

dbo.t_ICItemStandard user table no  no  FStdBatchQty

dbo.t_ICItemStandard user table no  no  FPOVAcctID

dbo.t_ICItemStandard user table no  no  FPIVAcctID

dbo.t_ICItemStandard user table no  no  FMCVAcctID

dbo.t_ICItemQuality  user table no  no  FItemID

dbo.t_ICItemQuality  user table no  no  FInspectionLevel

dbo.t_ICItemQuality  user table no  no  FInspectionProject

dbo.t_ICItemQuality  user table no  no  FIsListControl

dbo.t_ICItemQuality  user table no  no  FProChkMde

dbo.t_ICItemQuality  user table no  no  FWWChkMde

dbo.t_ICItemQuality  user table no  no  FIdentifier

dbo.t_ICItemQuality  user table no  no  FSOChkMde

dbo.t_ICItemQuality  user table no  no  FWthDrwChkMde

dbo.t_ICItemQuality  user table no  no  FStkChkMde

dbo.t_ICItemQuality  user table no  no  FOtherChkMde

dbo.t_ICItemQuality  user table no  no  FStkChkPrd

dbo.t_ICItemQuality  user table no  no  FStkChkAlrm

dbo.t_ICItemCustom   user table no  no  FItemID

dbo.t_ICItemCore  user table no  no  FItemID

dbo.t_ICItemCore  user table no  no  FModel

dbo.t_ICItemCore  user table no  no  FName

dbo.t_ICItemCore  user table no  no  FHelpCode

dbo.t_ICItemCore  user table no  no  FDeleted

dbo.t_ICItemCore  user table no  no  FShortNumber

dbo.t_ICItemCore  user table no  no  FPriceFixingType

dbo.t_ICItemCore  user table no  no  FSalePriceFixingType

dbo.t_ICItemCore  user table no  no  FPerWastage

dbo.t_ICItemCore  user table no  no  FARAcctID

dbo.t_ICItemCore  user table no  no  FPlanPriceMethod

dbo.t_ICItemCore  user table no  no  FPlanClass

dbo.t_ICItemCore  user table no  no  FOmortize

dbo.t_ICItemCore  user table no  no  FOmortizeScale

dbo.t_ICItemCore  user table no  no  FForSale

dbo.t_ICItemCore  user table no  no  FStaCost

dbo.t_ICItemCore  user table no  no  FOrderPrice

dbo.t_ICItemCore  user table no  no  FOrderMethod

dbo.t_ICItemCore  user table no  no  FNumber

dbo.t_ICItemCore  user table no  no  FModifyTime

dbo.t_ICItemCore  user table no  no  FParentID

dbo.t_ICItemCore  user table no  no  FBrNo

dbo.t_ICItemCore  user table no  no  FTopID

dbo.t_ICItemCore  user table no  no  FRP

dbo.t_ICItemBase  user table no  no  FItemID

dbo.t_ICItemBase  user table no  no  FErpClsID

dbo.t_ICItemBase  user table no  no  FUnitID

dbo.t_ICItemBase  user table no  no  FUnitGroupID

dbo.t_ICItemBase  user table no  no  FDefaultLoc

dbo.t_ICItemBase  user table no  no  FSPID

dbo.t_ICItemBase  user table no  no  FApproveNo

dbo.t_ICItemBase  user table no  no  FAuxClassID

dbo.t_ICItemBase  user table no  no  FTypeID

dbo.t_ICItemBase  user table no  no  FPreDeadLine

dbo.t_ICItemBase  user table no  no  FSerialClassID

dbo.t_ICItemBase  user table no  no  FSecUnitDecimal

dbo.t_ICItemBase  user table no  no  FAlias

dbo.t_ICItemBase  user table no  no  FOrderUnitID

dbo.t_ICItemBase  user table no  no  FSaleUnitID

dbo.t_ICItemBase  user table no  no  FStoreUnitID

dbo.t_ICItemBase  user table no  no  FProductUnitID

dbo.t_ICItemBase  user table no  no  FIsEquipment

dbo.t_ICItemBase  user table no  no  FEquipmentNum

dbo.t_ICItemBase  user table no  no  FIsSparePart

dbo.t_ICItemBase  user table no  no  FFullName

dbo.t_ICItemBase  user table no  no  FSecUnitID

dbo.t_ICItemBase  user table no  no  FSecCoefficient

dbo.t_ICItemBase  user table no  no  FSource

dbo.t_ICItemBase  user table no  no  FQtyDecimal

dbo.t_ICItemBase  user table no  no  FLowLimit

dbo.t_ICItemBase  user table no  no  FHighLimit

dbo.t_ICItemBase  user table no  no  FSecInv

dbo.t_ICItemBase  user table no  no  FUseState

 

--结果集:

name   type

dbo.cbAddCostObj  stored procedure

dbo.CheckItemReference   stored procedure

dbo.DeleteCostObj stored procedure

dbo.GenerateLowestBomCode   stored procedure

dbo.ICScmInfoUsed stored procedure

dbo.p_IC_InitCreditInstantData  stored procedure

dbo.p_ICGetDiscount_Info stored procedure

dbo.p_ICGetDiscount_Info_WB stored procedure

dbo.p_ICGetDiscount_NoInfo  stored procedure

dbo.p_ICGetDiscount_NoInfo_WB   stored procedure

dbo.p_ICGetPrice  stored procedure

dbo.p_ICGetPriceWriteBack   stored procedure

dbo.p_ICGetSupplyInfoEx  stored procedure

dbo.P_SCM_CHKDatePlanPriceToPlanAmount stored procedure

dbo.P_SCM_CHKDateSNBillToQtyNO1 stored procedure

dbo.P_SCM_CHKDateSNBillToQtyNO2 stored procedure

dbo.P_SCM_CHKDateSNBillToQtyNO5 stored procedure

dbo.P_SCM_CHKDateSNBillToQtyNO6 stored procedure

dbo.p_VASItemNeedQty stored procedure

dbo.prc_CheckItemForBid  stored procedure

dbo.prc_GetItemBatchQty  stored procedure

dbo.Prc_KFDateISNULL stored procedure

dbo.Prc_UseSecUnitCheckTOKEE    stored procedure

dbo.Proc_ICTransactBom   stored procedure

dbo.Rpt100 stored procedure

dbo.Rpt102 stored procedure

dbo.Rpt106 stored procedure

dbo.Rpt170 stored procedure

dbo.sp_ICObjectInUsed    stored procedure

dbo.v_CToIMapping view

dbo.v_FT_CIQAccount  view

dbo.v_FT_Inventory   view

dbo.v_FT_PurchaseOrder   view

dbo.v_FT_SaleOrder   view

dbo.v_FT_TurnOver view

dbo.v_IC_DeliveryNotice  view

dbo.v_IC_HistorySNStockOutInfo  view

dbo.v_IC_HistoryStockOutInfo    view

dbo.v_IC_ICSMInquiryMtrl view

dbo.v_IC_ICSMQuotation   view

dbo.v_IC_POOrder71   view

dbo.v_IC_QultyExpRpt1007309 view

dbo.v_IC_ReturnNotice    view

dbo.v_ICSMPurchaseAlter  view

dbo.v_LP_KanBanDetailInfo   view

dbo.v_OrderQuery_1007105 view

dbo.v_OrderQuery_1007130_1007131   view

dbo.v_OrderQuery_1007132 view

dbo.v_OrderQuery_21  view

dbo.v_OrderQuery_81  view

dbo.v_OrderQuery_81Head  view

dbo.v_OrderQuery_83_82   view

dbo.v_OrderQuery_85  view

dbo.v_OrderQuery1007100_1007105 view

dbo.v_SToIMapping view

dbo.View_PPBOM_Y02   view

dbo.View_PPBomChange view

dbo.vw_Device_Device view

dbo.vw_ICItoCDis  view

dbo.vw_ICItoCGroup   view

dbo.vw_ICItoCTDis view

dbo.vw_ICItoCTGroup  view

dbo.vw_ICItoEDis  view

dbo.vw_ICItoEGroup   view

dbo.vw_ICItoETDis view

dbo.vw_ICItoETGroup  view

dbo.vw_ICItoVDis  view

dbo.vw_ICItoVGroup   view

dbo.vw_ICPrcPly_CtoI view

dbo.vw_OutPut_ValidateEx view

dbo.vwErrStockBills  view

dbo.vwICBill_1    view

dbo.vwICBill_103  view

dbo.vwICBill_11   view

dbo.vwICBill_137  view

dbo.vwICBill_14   view

dbo.vwICBill_15   view

dbo.vwICBill_16   view

dbo.vwICBill_17   view

dbo.vwICBill_18   view

dbo.vwICBill_19   view

dbo.vwICBill_2    view

dbo.vwICBill_22   view

dbo.vwICBill_25   view

dbo.vwICBill_26   view

dbo.vwICBill_27   view

dbo.vwICBill_28   view

dbo.vwICBill_32   view

dbo.vwICBill_33   view

dbo.vwICBill_34   view

dbo.vwICBill_35   view

dbo.vwICBill_4    view

dbo.vwICBill_42   view

dbo.vwICBill_43   view

dbo.vwICBill_5    view

dbo.vwICBill_7    view

dbo.vwICBill_8    view

dbo.vwICBill_81   view

dbo.vwICInvBackup view

dbo.vwICInvInitial   view

dbo.vwICItemForSupply    view

dbo.vwPlanPriceHistory   view

dbo.vwPOOutStockBills    view

dbo.vwStockInBills   view

dbo.vwStockInInBills view

dbo.vwStockPDBills   view

dbo.vwStockPOOutBills    view

dbo.vwStockQuery  view

dbo.vwSupplyForICItem    view

*/

--1.通常的作法：

IF OBJECT_ID('tempdb..#t') IS NOT NULL

    DROP TABLE #t

GO

CREATE TABLE #t(name varchar(100),type varchar(100),updated varchar(100),selected varchar(100),[column] varchar(100))

INSERT #t EXEC testdb..sp_depends t_icitem

/*--因为返回的是两个结果集，结果出现如下提示：

在当前数据库中，指定的对象引用了以下内容:

在当前数据库中，以下内容引用了指定的对象:

消息213，级别16，状态7，过程sp_depends，第68 行

列名或所提供值的数目与表定义不匹配。

*/

 

--2.由于通常的作法达不到目的，今天在石头兄Blog得到血液，特作整理。

-- 通过以下语句可以只返回一个结果集，INTO到一个新表，还可以省去建表的麻烦，希望对后来者有帮助：

IF OBJECT_ID('tempdb..#t') IS NOT NULL

    DROP TABLE #t

GO

SELECT * 

INTO #t

FROM OPENROWSET('SQLNCLI','Trusted_Connection=yes','exec testdb..sp_depends t_icitem')

 

SELECT * FROM #t

/*

返回的结果同结果集

*/

 

--这样就可以灵活地使用过程返回的结果集了