我如何将存储过程的值插入表 [英] how do i insert values of stored procedure to table

问题描述

我有以下功能：

i have follow function:

Function [dbo].[FilterLedgerDocumentItemWithDateAndNumber] (
				@EnterpriseId uniqueidentifier,
				@FilterFromDate datetime2(7),
				@FilterToDate datetime2(7)
				
				)
				
RETURNS TABLE
AS
RETURN 
(Select CASE MONTH(ledger.CreateDate)
		When 1 then 'January'
		When 2 then 'February'
		When 3 then 'March'
		When 4 then 'April'
		When 5 then 'May'
		When 6 then 'June'
		When 7 then 'July'
		When 8 then 'August'
		When 9 then 'September'
		When 10 then 'October'
		When 11 then 'November'
		When 12 then 'December'
	End as MonthOfDocumentItem
	,case when Sum(ledger.Credit)>Sum(ledger.Debit)
			 then Sum(ledger.Credit)-Sum(ledger.Debit)
			 else 
			  Sum(ledger.Credit)-Sum(ledger.Debit)
			  	End as CredotDebit,ledger.AccountId,ledger.Title
		From GetLedgerDocumentItem(@EnterpriseId) as ledger
		Where	ledger.CreateDate >= @FilterFromDate And
				ledger.CreateDate <= @FilterToDate
				
				
		group by MONTH(ledger.CreateDate),ledger.AccountId,ledger.Title

		)

我将它的值插入表中：

and i insert value of it to table:

function [dbo].[SampleTr](@IsFilteDate bit,
						 @IsFilterDocumentNumber bit,
						 @EnterpriseId uniqueidentifier,
						 @FilterFromDate datetime2(7),
						 @FilterToDate datetime2(7)
						  
						) 
  returns @results table (
                          MonthOfDocumentItem datetime2(7),
						   AccountId nvarchar(50),
							CreditDebit decimal,
							Title nvarchar(150)
			 )
as
begin

		  if @IsFilteDate=1 and @IsFilterDocumentNumber = 1 
		  begin       
		   	insert @results (MonthOfDocumentItem,CreditDebit,AccountId,Title)
		    Select	asd.MonthOfDocumentItem,asd.CredotDebit,asd.AccountId,asd.Title
			From FilterLedgerDocumentItemWithDateAndNumber(@EnterpriseId,@FilterFromDate,@FilterToDate) as asd
		  end   
		  
		  return
end

error =& INSERT语句的选择列表包含的项目少于插入列表。 SELECT值的数量必须与INSERT列的数量相匹配。

error=&The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns.

推荐答案

看起来你的函数返回了多个列。为此

1.尝试使用数据执行数据库上的每个小部分

2.查看列超出的位置因为这个错误只是因为列值增加而不是插入命令中的预期

It looks that your function is returning more than one columns .For that
1. Try To execute each small part on database with data
2. see where columns exceeds bec'se this error only bec'se column values increase than expected in insert command

这篇关于我如何将存储过程的值插入表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！