Error when executing store procedure

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?
Raj_donetAsked:
Who is Participating?
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
^^^^  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
 
Jesus RodriguezConnect With a Mentor IT ManagerCommented:
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
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
>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
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
Jesus RodriguezIT ManagerCommented:
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
 
PadawanDBAConnect With a Mentor Operational DBACommented:
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
 
dannygonzalez09Connect With a Mentor Commented:
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
 
Raj_donetAuthor Commented:
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
 
Valliappan ANConnect With a Mentor Senior Tech ConsultantCommented:
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
 
Raj_donetAuthor Commented:
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
 
Raj_donetAuthor Commented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
Raj_donetAuthor Commented:
Thanks for all your suggestions.
the problem was with name conflict between System store procedure and the one I created.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.