Procedure has no parameters and arguments were supplied

Question

I receive this error when I run code to execute a stored procedure for SQL Server 2017: Procedure has no parameters and arguments were supplied. (8146)

My code is below and there may be multiple issues with it.

The goal with this stored procedure is to run the code within the select distinct block, truncate then insert the output into the CombinedOutput table which I have already created beforehand. Not sure if I need to declare a table that currently exists? Lastly, I want add a load date and load by so that I am aware when it last loaded.

ALTER PROCEDURE [bom].[VFBOMCalculations] AS

Begin
DECLARE @VFBOMCombinedOutput TABLE (
[supplier] varchar NULL,
[validity] varchar NULL,
[req_type] varchar NULL,
[status] varchar NULL,
[Notes] varchar NULL,
[req_date] varchar NULL,
[requestor] varchar NULL,
[approver] varchar NULL,
[ECC_WP] varchar NULL,
[deplete_ind] varchar NULL,
[parts_family_ind] varchar NULL,
[process] varchar NULL,
[oem_supplier] varchar NULL,
[oem_SPN] varchar NULL,
[oem_IPN] varchar NULL,
[oem_part_desc] varchar NULL,
[old_cat] varchar NULL,
[old_msq] varchar NULL,
[new_cat] varchar NULL,
[new_msq] varchar NULL,
[altRpr_supplier] varchar NULL,
[altRpr_SPN] varchar NULL,
[altRpr_IPN] varchar NULL,
[altRpr_desc] varchar NULL,
[tool_killer] varchar NULL,
[supplier_rpr_ind] varchar NULL,
[fb_lead_time] varchar NULL,
[rpr_lead_time] varchar NULL,
[uom] varchar NULL,
[consumable_ind] varchar NULL,
[PM_item_ind] varchar NULL,
[suplrQty_pertool] varchar NULL,
[machine_type] varchar NULL,
[complete_key] varchar NULL

		)   

--Truncate Table
TRUNCATE TABLE [bom].[VFBOMCombinedOutput]

--Populate table
INSERT INTO [bom].[VFBOMCombinedOutput]
(
[supplier]
,[validity]
,[req_date]
,[status]
,[Notes]
,[req_type]
,[requestor]
,[approver]
,[ECC_WP]
,[deplete_ind]
,[parts_family_ind]
,[process]
,[oem_supplier]
,[oem_SPN]
,[oem_IPN]
,[oem_part_desc]
,[old_cat]
,[old_msq]
,[new_cat]
,[new_msq]
,[altRpr_supplier]
,[altRpr_SPN]
,[altRpr_IPN]
,[altRpr_desc]
,[tool_killer]
,[supplier_rpr_ind]
,[fb_lead_time]
,[rpr_lead_time]
,[uom]
,[consumable_ind]
,[PM_item_ind]
,[suplrQty_pertool]
,[machine_type]
,[complete_key]
,[notvalid_key]
)

SELECT DISTINCT
A.[supplier]
,A.[validity]
,A.[req_type]
,A.[status]
,A.[Notes]
,A.[req_date]
,A.[requestor]
,A.[approver]
,A.[ECC_WP]
,A.[deplete_ind]
,A.[parts_family_ind]
,A.[process]
,A.[oem_supplier]
,A.[oem_SPN]
,A.[oem_IPN]
,A.[oem_part_desc]
,A.[old_cat]
,A.[old_msq]
,A.[new_cat]
,A.[new_msq]
,A.[altRpr_supplier]
,A.[altRpr_SPN]
,A.[altRpr_IPN]
,A.[altRpr_desc]
,A.[tool_killer]
,CASE
WHEN A.[supplier_rpr_ind] IN ('Yes', 'yes', 'y', 'YES', 'Y') Then 'Y'
WHEN A.[supplier_rpr_ind] IN ('No', 'no', 'n', 'NO', 'N') Then 'N'
ELSE A.[supplier_rpr_ind]
END AS [supplier_rpr_ind]
,A.[fb_lead_time]
,A.[rpr_lead_time]
,CASE
WHEN A.[uom] IN ('Yes', 'yes', 'y', 'YES', 'Y') Then 'Y'
WHEN A.[uom] IN ('No', 'no', 'n', 'NO', 'N') Then 'N'
ELSE A.[uom]
END AS [uom]
,A.[consumable_ind]
,CASE
WHEN A.[PM_item_ind] IN ('Yes', 'yes', 'y', 'YES', 'Y') Then 'Y'
WHEN A.[PM_item_ind] IN ('No', 'no', 'n', 'NO', 'N') Then 'N'
ELSE A.[PM_item_ind]
END AS [PM_item_ind]
,A.[suplrQty_pertool]
,A.[machine_type]
,A.[complete_key]
,A.[notvalid_key]
FROM [bom].[VFBOMIntake_Open] A
left outer join [bom].[VFBOMIntake_Complete] B
ON A.[notvalid_key] = B.[notvalid_key] AND A.[machine_type] = B.[machine_type] AND A.[process] = B.[process]
left outer join [bom].[VFBOMIntake_notValid] C
ON A.[notvalid_key] = C.[notvalid_key] AND A.[machine_type] = C.[machine_type] AND A.[process] = C.[process]
left outer join
(
SELECT [complete_key], [machine_type], [logged_SQL]
FROM [bom].[VFBOMIntake_Complete]
)D
ON A.[complete_key]+A.[machine_type] = D.[complete_key]+D.[machine_type]
AND D.[logged_SQL] IS NULL
AND (DATEDIFF(SECOND, LEFT(B.[logged_sql],10), LEFT(A.[logged_sql],10)) >= 0 OR B.[logged_sql] is NULL)
AND (DATEDIFF(SECOND, LEFT(C.[logged_sql],10), LEFT(A.[logged_sql],10)) >= 0 OR C.[logged_sql] is NULL)
AND DATEDIFF(DAY, getdate(), LEFT(A.[logged_sql], 10)) > -10
UNION ALL
SELECT DISTINCT
A.[supplier]
,A.[validity]
,A.[req_type]
,A.[status]
,A.[Notes]
,A.[req_date]
,A.[requestor]
,A.[approver]
,A.[ECC_WP]
,A.[deplete_ind]
,A.[parts_family_ind]
,A.[process]
,A.[oem_supplier]
,A.[oem_SPN]
,A.[oem_IPN]
,A.[oem_part_desc]
,A.[old_cat]
,A.[old_msq]
,A.[new_cat]
,A.[new_msq]
,A.[altRpr_supplier]
,A.[altRpr_SPN]
,A.[altRpr_IPN]
,A.[altRpr_desc]
,A.[tool_killer]
,CASE
WHEN A.[supplier_rpr_ind] IN ('Yes', 'yes', 'y', 'YES', 'Y') Then 'Y'
WHEN A.[supplier_rpr_ind] IN ('No', 'no', 'n', 'NO', 'N') Then 'N'
ELSE A.[supplier_rpr_ind]
END AS [supplier_rpr_ind]
,A.[fb_lead_time]
,A.[rpr_lead_time]
,CASE
WHEN A.[uom] IN ('Yes', 'yes', 'y', 'YES', 'Y') Then 'Y'
WHEN A.[uom] IN ('No', 'no', 'n', 'NO', 'N') Then 'N'
ELSE A.[uom]
END AS [uom]
,A.[consumable_ind]
,CASE
WHEN A.[PM_item_ind] IN ('Yes', 'yes', 'y', 'YES', 'Y') Then 'Y'
WHEN A.[PM_item_ind] IN ('No', 'no', 'n', 'NO', 'N') Then 'N'
ELSE A.[PM_item_ind]
END AS [PM_item_ind]
,A.[suplrQty_pertool]
,A.[machine_type]
,A.[complete_key]
,A.[notvalid_key]
FROM [bom].[VFBOMIntake_notValid] A
left outer join [bom].[VFBOMIntake_Complete] B
ON A.[notvalid_key] = B.[notvalid_key] AND A.[machine_type] = B.[machine_type] AND A.[process] = B.[process]
left outer join [bom].[VFBOMIntake_Open] C
ON A.[notvalid_key] = C.[notvalid_key] AND A.[machine_type] = C.[machine_type] AND A.[process] = C.[process]
left outer join
(
SELECT [complete_key], [machine_type], [logged_SQL]
FROM [bom].[VFBOMIntake_Complete]
)D
ON A.[complete_key]+A.[machine_type] = D.[complete_key]+D.[machine_type]
WHERE A.req_date NOT LIKE '%//%'
AND D.[logged_SQL] IS NULL
AND (DATEDIFF(SECOND, LEFT(B.[logged_sql],10), LEFT(A.[logged_sql],10)) >= 0 OR B.[logged_sql] is NULL)
AND (DATEDIFF(SECOND, LEFT(C.[logged_sql],10), LEFT(A.[logged_sql],10)) >= 0 OR C.[logged_sql] is NULL)
AND DATEDIFF(DAY, getdate(), LEFT(A.[logged_sql], 10)) > -10

UPDATE [bom].[VFBOMCombinedOutput]
SET [LoadDtm] = CAST(Format(GetDate(), 'yyyy-MM-dd HH:mm:ss') AS Datetime),
[LoadBy] = 'SQLServerEtl';

END

Answer 1

Hi,

Procedure has no parameters and arguments were supplied. (8146)

We have no idea how you execute your SP since you did not provided the command, but this I see that your SP has no input parameters and this error is pretty clear

It seems like when you call to run the SP you tried to use parameters but as mentioned, you SP has no input parameters

For example if you have such SP:

create or alter procedure SP1 as SELECT 1  
GO  
  
Execute SP1  
GO -- OK  
  
Execute SP1 @i = 1  
GO -- Issue! You cannot pass parameters to SP which has no setting for input parameters  

I highly recommend you to go over a good tutorial about creating Stored Procedure (from start to end) or at least if you are a bit more lazy then search for tutorials about: sql server create stored procedure with parameters

Here is one Google find for me:

https://learn.microsoft.com/sql/t-sql/statements/create-procedure-transact-sql?view=sql-server-ver16&WT.mc_id=DP-MVP-5001699#parameter_name

Or check this lesson (part of a great tutorial):

https://www.w3schools.com/sql/sql_stored_procedures.asp