Insert data in SQL Server stored procedure

Question

I am trying to add data in single table using a SQL Server stored procedure and I am getting an error

procedure or function x has too many argument specified

Also how can I get newly created ID of record as I need to update multiple tables using these IDs

I am using SQL Server 2012

Many thanks

ALTER PROCEDURE [dbo].[CreateNewFunctionsNavigation]
   @FunctionName nvarchar(250),
   @Hierarchy_Level int
AS
BEGIN
   SET NOCOUNT ON;

   INSERT [dbo].[Navigation_Functions] ([FunctionName], [Hierarchy_Level])
   VALUES(@FunctionName, @Hierarchy_Level)
END

Execution of stored procedure:

DECLARE @return_value int

EXEC    @return_value = [dbo].[CreateNewFunctionsNavigation]
        @FunctionName = N'DSD',
        @Hierarchy_Level = 3

SELECT  'Return Value' = @return_value
GO

My SQL for Function table

USE [MySolution01_DB]
GO

/****** Object:  Table [dbo].[Navigation_Functions]    Script Date: 06/01/2015 15:58:57 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Navigation_Functions](
 [Function_ID] [int] IDENTITY(1,1) NOT NULL,
 [FunctionName] [nvarchar](250) NOT NULL,
 [Hierarchy_Level] [int] NOT NULL,
CONSTRAINT [PK_Functions] PRIMARY KEY CLUSTERED 
(
  [Function_ID] ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,   ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

  GO

Mike · Accepted Answer · 2015-01-06 15:56:48Z

1

It should be

INSERT INTO [dbo].[Navigation_Functions] ([FunctionName], [Hierarchy_Level])

I created a sample table

CREATE TABLE [dbo].[Navigation_Functions](
    [NavigationFunctionId] [int] IDENTITY(1,1) NOT NULL,
    [FunctionName] [nvarchar](250) NULL,
    [Hierarchy_Level] [int] NULL,
 CONSTRAINT [PK_Navigation_Functions] PRIMARY KEY CLUSTERED 
(
    [NavigationFunctionId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

edited Jan 6, 2015 at 15:56

answered Jan 6, 2015 at 15:23

Mike

2,6017 gold badges36 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Mike Over a year ago

What's your table structure look like? Is your primary key column set to be the primary key? Is it an int type? If so, do you have Is Identity set to Yes under Identity Specification?

Mike Over a year ago

I added a sample script of the table creation.

K.Z Over a year ago

yes I have primary key FunctionID whose Identity set to yes

K.Z Over a year ago

i have updated my question with SQL Script for my table

Mike Over a year ago

Everything you posted worked for me in SQL Management Studio. I used it verbatim. Are you calling this from some other place like a web application?

|

AD.Net · Accepted Answer · 2015-01-06 15:23:25Z

1

You can get the last ID by: SELECT SCOPE_IDENTITY();

answered Jan 6, 2015 at 15:23

AD.Net

13.4k2 gold badges30 silver badges47 bronze badges

Comments

K.Z · Accepted Answer · 2015-01-06 16:04:14Z

0

USE [MySolution01_DB]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[CreateNewFunctionsNavigation]
  @FunctionName nvarchar(250),
  @Hierarchy_Level INT,
  @Function_identity INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;

INSERT INTO [dbo].[Navigation_Functions] ([FunctionName],[Hierarchy_Level] ) 
VALUES(@FunctionName, @Hierarchy_Level);

   SET @Function_identity=SCOPE_IDENTITY()
   RETURN @Function_identity

RETURN
END

answered Jan 6, 2015 at 16:04

K.Z

5,09527 gold badges120 silver badges268 bronze badges

Collectives™ on Stack Overflow

Insert data in SQL Server stored procedure

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related