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;
If (@Vendor !=1)
return
IF( @StartDate > @EndDate )
RETURN
DECLARE @StartDateRestricted TABLE (Restricted_Date DATE)
INSERT INTO @StartDateRestricted
EXEC [dbo].[GetStartDateRestriction]
IF ( @StartDate < (SELECT Restricted_Date FROM @StartDateRestricted))
RETURN
IF @StartDate <= DATEADD(YEAR, -1, @EndDate)
RETURN;
DECLARE @NewStartDate DATETIME = CONVERT(DATETIME, @StartDate)
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
SELECT
TV.VendorId AS VendorId,
TV.VendorName AS VendorName,
MWS.AccountId AS AccountId,
TblAccount.AccountName AS AccountName,
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,
TA.DisplayName,
TblAccount.AccountName
UNION
SELECT
VBF.VendorIDs AS VendorId,
TV.VendorName AS VendorName,
VBF.PrimaryId AS AccountId,
FA.name AS AccountName,
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 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
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