Solved

Error when executing store procedure

Posted on 2013-11-07
12
473 Views
Last Modified: 2013-11-11
I am getting this error when I excute below procedure
Msg 8144, Level 16, State 2, Procedure sp_adduser, Line 0
Procedure or function sp_adduser has too many arguments specified.

ALTER PROCEDURE [dbo].[sp_AddUser]
	@UserName varchar(10),
    @FirstName varchar(25),
    @LastName varchar(25),
    @Email varchar(50),
    @Manager varchar(50),
    @Notes varchar(1048)
AS
BEGIN

    INSERT INTO [SCM_Web].[dbo].[Users]
           (UserName
           ,[FirstName]
           ,[LastName]
           ,[Email]
           ,[Manager]
           ,[Notes]
           ,[DateCreated])
     VALUES
           (@UserName
           ,@FirstName
           ,@LastName
           ,@Email
           ,@Manager
           ,@Notes
           ,GETDATE())

    
END

Open in new window

When I insert using the insert statement, it is working fine, what did I do wrong?
0
Comment
Question by:Raj_donet
  • 4
  • 3
  • 2
  • +3
12 Comments
 
LVL 13

Assisted Solution

by:Jesus Rodriguez
Jesus Rodriguez earned 84 total points
ID: 39630958
Verify that none of the field are auto populated (datecreated for example can have a default value assigned to the sql) but also in the insert into try to put Username between [ ] like this

 INSERT INTO [SCM_Web].[dbo].[Users]
           ([UserName]
           ,[FirstName]
0
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 167 total points
ID: 39630961
>I am getting this error when I excute below procedure
Copy-paste the T-SQL code that executes this procedure into this question.
I'm counting six parameters in the SP.   Guessing the T-SQL call doesn't have six.
0
 
LVL 13

Expert Comment

by:Jesus Rodriguez
ID: 39630966
If your running this from outside check the apostrophe on the parameters that you pass or check that your not passing the date also as a parameter because it got the date from the Sp directly
0
 
LVL 10

Assisted Solution

by:PadawanDBA
PadawanDBA earned 83 total points
ID: 39630969
Are you getting an error when you try to alter the procedure or when you execute the procedure ?  If the latter, can you post the TSQL that you are using to exec the sp ?

Edit: i was going the same route as Jim on this one - my guess is a mismatch of either not having all the parameters or calling with too many parameters.
0
 
LVL 5

Assisted Solution

by:dannygonzalez09
dannygonzalez09 earned 83 total points
ID: 39631085
Change the word - "value" with your values

EXEC sp_adduser 
@UserName  = value,
    @FirstName =value,
    @LastName = value,
    @Email = value,
    @Manager = value,
    @Notes = value

Open in new window

0
 

Author Comment

by:Raj_donet
ID: 39631147
Whe I execute the storeprocedure from from sql server/tsql also I am getting the same error.
USE [db]
GO

DECLARE	@return_value int

EXEC	@return_value = [dbo].[sp_AddUser]
		@UserName = N'abcd',
		@FirstName = N'abcd',
		@LastName = N'abcd',
		@Email = N'abcd@abcd.com',
		@Manager = N'abcd',
		@Notes = N'admin access'

SELECT	'Return Value' = @return_value

GO

Open in new window


Msg 8144, Level 16, State 2, Procedure sp_adduser, Line 0
Procedure or function sp_adduser has too many arguments specified.

(1 row(s) affected)
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 65

Accepted Solution

by:
Jim Horn earned 167 total points
ID: 39631171
^^^^  Nowhere in the SP does it return a set, so you can't do EXEC @return_value = call the SP.

If you're trying to grab the identity column of the insert, then you'd have to do it by adding a seventh parameter @id as an output parameter, like this:
ALTER PROCEDURE dbo.sp_AddUser
	@UserName varchar(10),
    @FirstName varchar(25),
    @LastName varchar(25),
    @Email varchar(50),
    @Manager varchar(50),
    @Notes varchar(1048),
    @return_value int
AS
BEGIN

INSERT INTO SCM_Web.dbo.Users
   (UserName, FirstName, LastName, Email, Manager, Notes, DateCreated)
VALUES
   (@UserName, @FirstName, @LastName, @Email, @Manager, @Notes, GETDATE())

-- Return Value
SELECT @return_value = SCOPE_IDENTITY()
    
END 

Open in new window


Then call it like this..
DECLARE	@return_value int

EXEC sp_AddUser @UserName = N'abcd',@FirstName = N'abcd',@LastName = N'abcd',@Email = N'abcd@abcd.com',@Manager = N'abcd',@Notes = N'admin access', @return_value

SELECT	'Return Value' = @return_value

Open in new window

0
 
LVL 9

Assisted Solution

by:Valliappan AN
Valliappan AN earned 83 total points
ID: 39631173
In your execute query, you used 'Use db', whereas in the stored procedure, [SCM_Web] database is been inserted to.

Are you using the right database names in both places, in normal cases, both, 'Use [db]' and 'db..' would not be used, and it will be in the local database from which the query is used.

Its possible, that the procedure, is been run from another database ('db' here), where the sp_adduser, has lesser number of parameters.

Please check the code, or try doing sp_helptext sp_adduser, to see the exact procedure code.

HTH.
0
 

Author Comment

by:Raj_donet
ID: 39631258
It is not the problem with with DB name, I forgot the change [SCM_Web] database name when I pasted the code in experts-exchange.
I solved it, it was because there is a SP in system SP with same name, it got confused, when the changed my SP name. It worked fine, thanks for all your help.
0
 

Author Comment

by:Raj_donet
ID: 39639453
I've requested that this question be closed as follows:

Accepted answer: 0 points for Raj_donet's comment #a39631258

for the following reason:

I solved it, it was because there is a SP in system SP with same name, it got confused, when the changed my SP name. It worked fine, thanks for all your help.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39631288
Raj_donet - Regardless of database name, there are still issues with the SP call, which looks like has been addressed by multiple experts here.  Any chance you can reward some of them for their contributions?

Unless any expert objects, I'm going to recommend a delete-no refund on this question, as 'I'm an idiot, sorry guys, no soup for you' is not an accept-worthy solution.
0
 

Author Closing Comment

by:Raj_donet
ID: 39639454
Thanks for all your suggestions.
the problem was with name conflict between System store procedure and the one I created.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video discusses moving either the default database or any database to a new volume.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now