Solved

Error when executing store procedure

Posted on 2013-11-07
12
488 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 66

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 66

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 66

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

Industry Leaders: 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!

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

615 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