stored procedure error

Question

0.00/5 (No votes)

See more:

SQL-Server-2012

i have written following stored procedure:
in which there are 2 tables arts and artist.artistId is a foreign key in arts table.

create procedure spInsertArts(
     @artsId int out,
     @name varchar(50),
     @category varchar(50),
     @artistId int
     )
     as
          begin
              declare @artistId int
		set @artistId=@@IDENTITY
			
            if exists(select artistId from artist)
              insert into arts(name,category,artistId) values(@name,@category,@artistId) 
           end

I am getting error as:

Procedure or function spInsertArts has too many arguments specified.

please help

Posted 23-Apr-14 4:44am

Member 10724668

Updated 23-Apr-14 5:07am

v3

Add a Solution

Comments

thatraja 23-Apr-14 10:47am

how did you call the stored procedure? Show that portion too

Member 10724668 23-Apr-14 10:50am

cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@name", TextBox1.Text);
cmd.Parameters.AddWithValue("@category", DropDownList1.SelectedValue);
SqlParameter output = new SqlParameter();
output.ParameterName = "@artsId";
output.SqlDbType = System.Data.SqlDbType.Int;
output.Direction = System.Data.ParameterDirection.Output;
cmd.Parameters.Add(output);
con.Open();
cmd.ExecuteNonQuery();

2 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2014-04-23T05:09:00

Solution 1

Um...You declare four parameters, but you only provide three in your call - and in the SP you try to declare a variable with the same name as one of the parameters: @artistId

I suspect that you didn't mean to include @artistId in the parameters list.

Posted 23-Apr-14 5:09am

OriginalGriff

Comments

thatraja 23-Apr-14 11:11am

aaarggg.....beat me to it. 5!

Member 10724668 23-Apr-14 11:17am

I have removed @artistId from parameter list but still I am getting error

OriginalGriff 23-Apr-14 11:25am

What happens if you execute it within SSMS?

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) · Answer 2 · 2014-04-23T05:10:00

Solution 2

Quote:

C#

cmd.CommandType = System.Data.CommandType.StoredProcedure;

cmd.Parameters.AddWithValue("@name", TextBox1.Text);
cmd.Parameters.AddWithValue("@category", DropDownList1.SelectedValue);

SqlParameter output = new SqlParameter();
output.ParameterName = "@artsId";
output.SqlDbType = System.Data.SqlDbType.Int;
output.Direction = System.Data.ParameterDirection.Output;

cmd.Parameters.Add(output);
con.Open();
cmd.ExecuteNonQuery();

You have not passed the parameter "@artistId" in your code. Please pass that and try.

Posted 23-Apr-14 5:10am

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ)

Comments

Member 10724668 23-Apr-14 11:15am

@artistId is a foreign key. And i want it to get value of latest artistId of artist table.

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 23-Apr-14 11:18am

That you have already declared inside the procedure. So, no need to declare that as a parameter. Remove that from parameters in the Stored Procedure.

Member 10724668 23-Apr-14 11:23am

yes i have removed it.But I am getting Same error

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 23-Apr-14 11:36am

As I observe, you have made the procedure unnecessarily complex. Moreover, you are not using @artsId, but passing it as a parameter. The simplified version of your Stored Procedure would look like...

create procedure spInsertArts(
@name varchar(50),
@category varchar(50)
)
as
begin

if exists(select artistId from artist)
insert into arts(name, category, artistId) values(@name, @category, @@IDENTITY)
end

stored procedure error

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

stored procedure error

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us