CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Alex Gordon
Alex Gordon

Reputation: 60691

Invalid column name error when creating a stored procedure

I am trying to create a stored procedure but am getting these errors:

Msg 207, Level 16, State 1, Procedure Insert_QuickLabDump, Line 42
Invalid column name 'Date_Collected'.
Msg 207, Level 16, State 1, Procedure Insert_QuickLabDump, Line 43
Invalid column name 'Time_Collected'.
Msg 207, Level 16, State 1, Procedure Insert_QuickLabDump, Line 43
Invalid column name 'Date_Entered'.
Msg 207, Level 16, State 1, Procedure Insert_QuickLabDump, Line 44
Invalid column name 'Time_Entered'.
Msg 207, Level 16, State 1, Procedure Insert_QuickLabDump, Line 45
Invalid column name 'Date_Completed'.
Msg 207, Level 16, State 1, Procedure Insert_QuickLabDump, Line 46
Invalid column name 'Time_Completed'.
Msg 207, Level 16, State 1, Procedure Insert_QuickLabDump, Line 47
Invalid column name 'Test_Date'.
Msg 207, Level 16, State 1, Procedure Insert_QuickLabDump, Line 48
Invalid column name 'Test_Time'.

here is the full source:

USE [SalesDWH]
GO

/****** Object:  StoredProcedure [dbo].[Insert_QuickLabDump]    Script Date: 12/22/2011 14:52:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER on
GO

-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
create PROCEDURE [dbo].[Insert_QuickLabDump]
    -- Add the parameters for the stored procedure here
    @Specimen_ID [varchar](50),
    @Client_Key int,
    @Outcome [varchar](50),
    @Medications [varchar] (max),
    @Date_Collected date,
@Time_Collected time ,
@Date_Entered date,
@Time_Entered time ,
@Date_Completed date,
@Time_Completed time ,
@Test_Date date ,
@Test_Time time ,

    @Practice_Name [varchar] (500),
    @Practice_Code [varchar] (500),
    @Client_ID [varchar] (500),
    @Requesting_Physician [varchar] (500),
    @Other_Medications [varchar] (max),
    @Order_Comments [varchar] (max),
    @Reference_Number [varchar] (500),
    @Order_Count int

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    INSERT INTO [SalesDWH].[dbo].[QuickLabDump]
           ([Specimen ID]
           ,[Client Key]
           ,[Outcome]
           ,[Medications]
           ,[Date_Collected]
           ,[Time_Collected]
           ,[Date_Entered]
           , [Time_Entered]
           , Date_Completed
           , Time_Completed
           , Test_Date
           , Test_Time
           ,[Practice Name]
           ,[Practice Code]
           ,[Client ID]
           ,[Requesting Physician]
           ,[Other Medications]
           ,[Order Comments]
           ,[Reference Number]
           ,[Order Count]
     )
     VALUES
           (@Specimen_ID,
@Client_Key,
@Outcome,
@Medications,
@Date_Collected ,
@Time_Collected ,
@Date_Entered,
@Time_Entered ,
@Date_Completed ,
@Time_Completed,
@Test_Date ,
@Test_Time,
@Practice_Name,
@Practice_Code,
@Client_ID,
@Requesting_Physician,
@Other_Medications,
@Order_Comments,
@Reference_Number,
@Order_Count
)


END

What am I doing wrong?

here is the table structure:

USE [SalesDWH]
GO

/****** Object:  Table [dbo].[QuickLabDump]    Script Date: 12/22/2011 15:13:40 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[QuickLabDump](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [Specimen ID] [varchar](50) NOT NULL,
    [Client Key] [int] NOT NULL,
    [Outcome] [varchar](50) NOT NULL,
    [Medications] [varchar](max) NULL,
    [Date Collected] [date] NOT NULL,
    [Time Collected] [time](7) NOT NULL,
    [Date Entered] [date] NOT NULL,
    [Time Entered] [time](7) NOT NULL,
    [Date Completed] [date] NOT NULL,
    [Time Completed] [time](7) NOT NULL,
    [Test Date] [date] NOT NULL,
    [Test Time] [time](7) NOT NULL,
    [Practice Name] [varchar](500) NOT NULL,
    [Practice Code] [varchar](500) NOT NULL,
    [Client ID] [varchar](500) NULL,
    [Requesting Physician] [varchar](500) NULL,
    [Other Medications] [varchar](max) NULL,
    [Order Comments] [varchar](max) NULL,
    [Reference Number] [varchar](500) NULL,
    [Order Count] [int] NOT NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Upvotes: 4

Views: 26246

Answers (4)

JeffO
JeffO

Reputation: 8043

[Date Collected] [date] NOT NULL, is not the same as Msg 207, Level 16, State 1, Procedure Insert_QuickLabDump, Line 42 Invalid column name 'Date_Collected'.

Upvotes: 2

D&#39;Arcy Rittich
D&#39;Arcy Rittich

Reputation: 171361

You are using [Date_Collected] in your stored procedure, but the actual column name according to the CREATE TABLE statement is [Date Collected] (no underscore).

Upvotes: 5

Jacob
Jacob

Reputation: 78850

The errors are saying that there are no such columns (for example Date_Collected) in the QuickLabDump table. For insert statements, you have to have the right column names. You have a column named Date Collected, not Date_Collected, for example (note the space instead of the underscore).

Upvotes: 9

Steve Wellens
Steve Wellens

Reputation: 20620

It looks like you added those columns to an existing procedure. Have you added the columns to the database table you are trying to insert to?

Upvotes: 7

Related Questions