Invalid column name 'last4SSN'.

Stored procedure error:
ALTER PROCEDURE [dbo].[CreateAdditionalAccountInfo] 
	@UserID uniqueidentifier, 
	@FirstName varchar(50),
	@LastName varchar(50),
	@Degree varchar(50),
	@Organization varchar(10),
	@Phone char(10),
	@Ext char(10) = '',
	@last4SSN nchar(4)
AS
BEGIN
	
	SET NOCOUNT ON;

	INSERT INTO [User_ExtraInfo] ([UserId], [FirstName], [LastName], [Degree], [Organization], [Phone], [Ext], [last4SSN]) VALUES (@UserId, @FirstName, @LastName, @Degree, @Organization, @Phone, @Ext, @last4SSN)
END

Open in new window

zhshqzycAsked:
Who is Participating?
 
shekhar_shashiCommented:
I created this table based on the input parameters of the stored procedure.

USE [NGJISdev]
GO

/****** Object:  Table [dbo].[User_ExtraInfo]    Script Date: 12/27/2011 13:50:46 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[User_ExtraInfo](
      [UserID] [uniqueidentifier] NOT NULL,
      [FirstName] [varchar](50) NOT NULL,
      [LastName] [varchar](50) NOT NULL,
      [Degree] [varchar](50) NOT NULL,
      [Organization] [varchar](10) NULL,
      [Phone] [char](10) NULL,
      [Ext] [char](10) NULL,
      [last4SSN] [char](4) NOT NULL,
 CONSTRAINT [PK_CreateAdditionalAccountInfo] PRIMARY KEY CLUSTERED
(
      [UserID] 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

SET ANSI_PADDING ON
GO

Then I tried to compile the stored procedure:

ALTER PROCEDURE [dbo].[CreateAdditionalAccountInfo]
      @UserID uniqueidentifier,
      @FirstName varchar(50),
      @LastName varchar(50),
      @Degree varchar(50),
      @Organization varchar(10),
      @Phone char(10),
      @Ext char(10) = '',
      @last4SSN nchar(4)
AS
BEGIN
      
      SET NOCOUNT ON;

      INSERT INTO [User_ExtraInfo] ([UserId], [FirstName], [LastName], [Degree], [Organization], [Phone], [Ext], [last4SSN]) VALUES (@UserId, @FirstName, @LastName, @Degree, @Organization, @Phone, @Ext, @last4SSN)
END

It compiled without any issues.

I was able to execure the stored procedure with the following script.

USE [NGJISdev]
GO

DECLARE      @return_value int

EXEC      @return_value = [dbo].[CreateAdditionalAccountInfo]
            @UserID = '9F9F7756-6D34-4D0B-82AB-281DF92FF62B',
            @FirstName = N'John',
            @LastName = N'Smith',
            @Degree = N'MS (Psychology)',
            @Organization = N'XYZ',
            @Phone = N'1111111111',
            @Ext = N'112',
            @last4SSN = N'1234'

SELECT      'Return Value' = @return_value

GO

It inserted one record into the table. So I am not sure if I am missing something here. I am using SQL 2008 R2.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
<wild guess>
Make sure table User_ExtraInfo has a column named last4SSN, with a column type of nchar(4).
0
 
zhshqzycAuthor Commented:
The original table didn't have the last column "last4SSN", the I maually added it to the table then run the script.
 selectAnything wrong when I altered the table design?
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
after the table design change, rerun the alter procedure eventually, to validate that you have both the changed table and the procedure in the same database.
0
 
zhshqzycAuthor Commented:
I closed Management Studio and reopened it. Now it is good.
0
 
zhshqzycAuthor Commented:
Thanks for many input.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.