Procedure has no parameters and arguments were supplied

AB 21 Reputation points
2022-11-12T06:29:50.897+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,279 questions
SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,861 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,597 questions
0 comments No comments
{count} votes

Accepted answer
  1. Ronen Ariely 15,101 Reputation points
    2022-11-12T10:01:31.41+00:00

    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  
    

    259724-image.png

    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

    0 comments No comments

0 additional answers

Sort by: Most helpful