Solved

Error when executing store procedure

Posted on 2013-11-07
12
480 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

831 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