Procedure has no Parameters and Arguments were Supplied.


Oh this is silly error , Junior DBA was execution one procedure shared by application team and he got this error. He was clueless and he was new he didn't know what to do.
Error:
Msg 8146, Level 16, State 2, Procedure xxxx, Line 0
Procedure xxx has no parameters and arguments were supplied.


Let check whats the issue.
I created one stored procedure 'Error_test' , Below is the script

After deploying I executed procedure below and here is my error. Error clearly says procedure has no parameter. What does this means???

Procedure is taking "GO" as parameter , So make sure while calling you should write any keyword or any variable which can be consider as parameter


So what's the correct way. You must put GO in second line or press enter just before "GO" and issue resolved
So junior DBA , Please read errors any you can fix it by your self

4 comments:

  1. How to pass argument to SP and access it inside to procedure?

    ReplyDelete
    Replies
    1. Reference: http://msdn.microsoft.com/en-us/library/ms187926.aspx

      CREATE PROCEDURE usp_add_kitchen @dept_id int, @kitchen_count int NOT NULL
      WITH EXECUTE AS OWNER, SCHEMABINDING, NATIVE_COMPILATION
      AS
      BEGIN

      UPDATE dbo.Departments
      SET kitchen_count = ISNULL(kitchen_count, 0) + @kitchen_count
      WHERE id = @dept_id
      END;

      Delete
  2. Thanks a lot it is working...Grt

    ReplyDelete