Why the Err occurred : "Procedure has no parameters and arguments were supplied" in SQL Server ?

Posted by PandianS under Error and Solution on 7/16/2010 | Views : 5538 | Status : [Member] [MVP] | Replies : 2

Creating a Stored Procedure:
IF OBJECT_ID('USP_Proc1','P') IS NOT NULL
DROP PROC USP_Proc1
GO
CREATE PROC USP_Proc1
AS
BEGIN
SELECT 'DotnetFunda' SQLServer
END
GO
What is a Batch ?
When we execute multiple statements as a whole, The whole statements called as a Batch.

Executing the following as a batch :
USP_Proc1
USP_Proc1
Go
It will throw an Err: "Procedure USP_Proc1 has no parameters and arguments were supplied"

BOL Rules:
If an EXECUTE statement is the first statement in a batch, the EXECUTE keyword is not required. The EXECUTE keyword is required if the EXECUTE statement is not the first statement in the batch.

According to the BOL rule : In our batch, "EXEC" is not necessary for first line, It is mandatory on Second Line.
USP_Proc1
Exec USP_Proc1
Go
Result:
SQLServer
DotnetFunda

SQLServer
DotnetFunda

Conclusion:
BOL Rule: "If an EXECUTE statement is the first statement in a batch, the EXECUTE keyword is not required. The EXECUTE keyword is required if the EXECUTE statement is not the first statement in the batch."

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions



Responses

Posted by: Neeks on: 7/16/2010 [Member] Bronze

Up
0
Down
While you are executing the batch you have to put the execution delimiter between two procedure call or statement.

SQL is not delimited by the Line Break.

So you have to put "GO" between two procedure calls continuous,

USP_Proc1
GO
USP_Proc1
Go


This might help you...

Thanks,

PandianS, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: PandianS on: 7/16/2010 [Member] [MVP] Silver

Up
0
Down
The Scenario is How to execute Multiple Procedures as BATCH.

If you put GO between each other then, It will not be called as a BATCH.

Correct ?

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

PandianS, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response