Use stored procedure to insert some data into a table

Question

I am trying to create stored procedure that inserts some data into my table, but I'm getting some errors like

Invalid Column name

For all the columns that I specified in my stored procedure. I have an IDENTITY COLUMN called ID which increments by one each time record is inserted. I also have some other columns in the table but they can be null. Here is my stored procedure and cannot figure out what I am doing wrong here.

USE MYDB
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_Test]
  @myID bigInt,
  @myFirstName nvarchar(50)
  ,@myLastName nvarchar(50)
  ,@myAddress nvarchar(MAX)
   ,@myPort int

AS 
BEGIN 

 SET NOCOUNT ON; 

  BEGIN 

insert into MYDB.dbo.MainTable (MyID, MyFirstName, MyLastName, MyAddress, MyPort)
values(@myID, @myFirstName, @myLastName, @myAddress, @myPort)

  END 

END
GO

Here is the table definition:

USE [MYDB]
GO

/****** Object:  Table [dbo].[MainTable]    Script Date: 01/03/2013 11:17:17 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[MainTable](
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [MyID] [bigint] NULL,
    [MyFirstName] [nvarchar](50) NULL,
    [MyLastName] [nvarchar](50) NULL,
    [MyAddress] [nvarchar](max) NULL,
    [MyPort] [int] NULL,
    [MyZipCode] [nchar](10) NULL,
    [CompName] [nvarchar](50) NULL
) ON [PRIMARY]

GO

Solution

If you have the table definition to have an IDENTITY column e.g. IDENTITY(1,1) then don't include MyId in your INSERT INTO statement. The point of IDENTITY is it gives it the next unused value as the primary key value.

insert into MYDB.dbo.MainTable (MyFirstName, MyLastName, MyAddress, MyPort)
values(@myFirstName, @myLastName, @myAddress, @myPort)

There is then no need to pass the @MyId parameter into your stored procedure either. So change it to:

CREATE PROCEDURE [dbo].[sp_Test]
@myFirstName nvarchar(50)
,@myLastName nvarchar(50)
,@myAddress nvarchar(MAX)
,@myPort int

AS 

If you want to know what the ID of the newly inserted record is add

SELECT @@IDENTITY

to the end of your procedure. e.g. http://msdn.microsoft.com/en-us/library/ms187342.aspx

You will then be able to pick this up in which ever way you are calling it be it SQL or .NET.

P.s. a better way to show you table definision would have been to script the table and paste the text into your stackoverflow browser window because your screen shot is missing the column properties part where IDENTITY is set via the GUI. To do that right click the table 'Script Table as' --> 'CREATE to' --> Clipboard. You can also do File or New Query Editor Window (all self explanitory) experient and see what you get.

Comments

[Taryn]

MyID is not the identity field. The identity field is id.

[user1858332]

Yes, that is true MyID is not the identity. The Identity column is called ID. I already updated the table definition with script. thanks

[Matthew R]

Well in that case I've just scripted your code into a new database and run the stored procedure. The record inserted for me.

[KM.]

never use @@IDENTITY, use SCOPE_IDENTITY(), do a google search if you need to ask why.