从varchar到int的转换失败 [英] Conversion failed from varchar to int

问题描述

我有一个存储过程，但是当我执行该过程时，出现错误从varchar(1,22)到int的转换失败".请帮助我删除此错误

I have a stored procedure, but when i execute it gives an error "Conversion failed from varchar(1,22) to int". Please help me remove this error

CREATE PROCEDURE [dbo].[GetValues]
 (
	@StartDate Date,
	@EndDate   Date,
	@Vendor INT, 	@VendorId INT	)
    
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
  
    If (@Vendor !=1)
    return
  

 -- If start Date is greater than End Date, return         
        IF( @StartDate > @EndDate )
             RETURN    

     DECLARE @StartDateRestricted TABLE (Restricted_Date DATE)
        -- Get Restricted date from Stored Procedure.
          INSERT INTO @StartDateRestricted
                  EXEC [dbo].[GetStartDateRestriction]

      -- If Start date is less than restricted date, return
            IF ( @StartDate < (SELECT Restricted_Date FROM @StartDateRestricted))
                  RETURN

      --Start date and End date must be within 1 year of date range.
            IF @StartDate <= DATEADD(YEAR, -1, @EndDate)
                  RETURN;
      
     
      -- Start Date is converted from DATE type to DATETIME.
		DECLARE @NewStartDate DATETIME = CONVERT(DATETIME, @StartDate)

		-- @NewEndDate is defined to make sure we fetch all the data for the last date 
		-- ie from (00:00:00 hrs to 23:59:59)
		DECLARE	@NewEndDate DATETIME = DATEADD(DAY, 1, @EndDate)
		SET		@NewEndDate = DATEADD(SECOND, -1, @NewEndDate)

	
DECLARE @TblAccount TABLE(AccountId INT, AccountName Varchar(100))

INSERT INTO @TblAccount
SELECT MPA.AccountId AS AccountId ,MPA.AccountName COLLATE DATABASE_DEFAULT AS AccountName FROM BingMapsPlatform_Staging.dbo.MapPointAccounts As MPA WHERE MPA.CustomerTypeId IN (2, 7, 8, 10, 11)
UNION
SELECT FA.new_accountid AS AccountId,FA.name  COLLATE DATABASE_DEFAULT AS AccountName FROM BingMapsPlatform_MSCRM.dbo.FilteredAccount AS FA WHERE FA.new_ownertype IN (2, 7, 8, 10, 11)

DECLARE @ResultSet Table(VendorId INT,VendorName Varchar(100),AccountId INT,AccountName Varchar(100),APIName Varchar(50),TransactionCount INT) 

INSERT INTO @ResultSet
--Table1 Data
SELECT
      TV.VendorId AS VendorId,
      TV.VendorName AS VendorName,
      MWS.AccountId AS AccountId,
      TblAccount.AccountName AS AccountName,      
     -- MWS.APICode AS APICode,
      TA.DisplayName AS APIName,       
      SUM(MWS.TransactionCount) AS TransactionCount
FROM 
      BingMapsPlatform_Staging.dbo.MWSDailyAggTrans  AS MWS with (Nolock)      
      INNER JOIN LoggingLookup.[dbo].[TblVendor] AS TV
      ON MWS.VendorId = TV.VendorId
      INNER JOIN LoggingLookup.[dbo].TblAPI AS TA
      ON MWS.APICode = TA.ApiCode
       INNER JOIN  @TblAccount AS TblAccount
      ON MWS.AccountId=TblAccount.AccountId 
      WHERE   (MWS.APIBillType in (1,2))
      AND MWS.EnvironmentId =0
      AND MWS.TransactionDate BETWEEN @NewStartDate AND @NewEndDate
      AND (MWS.VendorId LIKE '%11%')
      AND (MWS.VendorId NOT LIKE '%111%')    
	GROUP BY TV.VendorId,
	TV.VendorName, 
	MWS.AccountId, 
	MWS.APICode, 
	--MWS.APICode,
	TA.DisplayName,
	TblAccount.AccountName


UNION

--Table2 Data

SELECT
      VBF.VendorIDs AS VendorId,
      TV.VendorName AS VendorName,	        
      VBF.PrimaryId AS AccountId,
      FA.name AS AccountName,     
      --VBF.APICode AS APICode,
      TA.ApiName AS APIName,      
      ROUND(SUM(ISNULL(VBF.TransactionCount,0)),0) AS BillableTransactionCount
FROM 
      BingMapsPlatform_Staging.dbo.VEWSDailyAggTrans  AS VBF WITH (NOLOCK)
      INNER JOIN LoggingLookup.dbo.TblVendor TV WITH (NOLOCK)
      ON ([BingMapsPlatform_Staging].[dbo].[FnIfNumberExists](VBF.VendorIDs,',',11)=TV.VendorId OR [BingMapsPlatform_Staging].[dbo].[FnIfNumberExists](VBF.VendorIDs,',',111)=TV.VendorId)
      INNER JOIN BingMapsPlatform_MSCRM.dbo.FilteredAccount FA WITH (NOLOCK)
      ON VBF.PrimaryId = FA.new_accountid      
      INNER JOIN LoggingLookup.[dbo].TblAPI AS TA WITH (NOLOCK)
      ON VBF.APICode = TA.ApiCode
      WHERE 
      (VBF.APIBillType in (1,2))
      AND VBF.ServerRole =0
      AND (VBF.VendorIDs LIKE '%11%')
      AND (VBF.VendorIDs NOT LIKE '%111%')      
      AND VBF.Date BETWEEN @NewStartDate AND @NewEndDate
      --AND TTE.New_TicketTypeBillingStatus = 1
      --AND (VBF.PrimaryId IN (SELECT new_accountid FROM BingMapsPlatform_MSCRM.dbo.FilteredAccount  WHERE new_ownertype IN (2, 7, 8, 10, 11)))
      AND Credentials IN (SELECT FAPP.new_new_appid FROM BingMapsPlatform_MSCRM.dbo.FilteredNew_application FAPP
	INNER JOIN BingMapsPlatform_MSCRM.dbo.New_tickettypeExtensionBase TTE
	ON FAPP.new_tickettypeId = TTE.New_TickettypeId
	WHERE TTE.New_TicketTypeBillingStatus = 1
	AND FAPP.new_accountid IN (SELECT accountid FROM BingMapsPlatform_MSCRM.dbo.FilteredAccount WHERE new_ownertype IN (2, 7, 8, 10, 11)))
	GROUP BY VBF.VendorIds,
	VBF.PrimaryId,
	VBF.APICode,
	FA.name,
	TA.APIName,
	TV.VendorName
	--ORDER BY VBF.PrimaryId



SELECT   cast(VendorId as Int), cast(VendorName as int), cast(AccountId as int),cast(AccountName as Int),APIName ,SUM(TransactionCount) AS TransactionCount     
 FROM @ResultSet 
 GROUP BY  VendorId, VendorName, AccountId,AccountName,APIName
 ORDER BY AccountId
       

END
GO

推荐答案

没有表就不可能确定； e定义，但我会怀疑这一行:

It''s not possible to be sure without the tabl;e definitions, but I would suspect this line:

SELECT   cast(VendorId as Int), cast(VendorName as int), cast(AccountId as int),cast(AccountName as Int),APIName ,SUM(TransactionCount) 

我怀疑VendorName和AccountName是整数值-名称确实暗示非数字字符，这将导致您描述的错误消息

I doubt that VendorName and an AccountName are integer values - the names do imply non-numeric characters which would cause the error message you describe.

请在sql下方更改

Please change below sql

SELECT   cast(VendorId as Int), cast(VendorName as int), cast(AccountId as int),cast(AccountName as Int),APIName ,SUM(TransactionCount) 

到

to

SELECT   convert(Int, VendorId  ), convert(int , VendorName), convert(int , AccountId),convert(int, AccountName),APIName ,SUM(TransactionCount)

这篇关于从varchar到int的转换失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！