Solved

Error when executing store procedure

Posted on 2013-11-07
12
483 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

713 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