CODEWITHSUNDEEP
sql-serverstored-procedures
Olaleru Isaiah Olusoji
Olaleru Isaiah Olusoji

Reputation: 47

Insert stored procedure is not compiling

Visual studio 2005 is still asking me for a create or alter after writing create procedure. What could be wrong?

USE metroengineeringdatabase

GO
CREATE PROCEDURE dbo.InsertRecords (
@assetcodeId nvarchar(20),
@name_of_asset nvarchar(20),
@unit_no nvarchar(20),
@manufacturer nvarchar(20),
@make nvarchar(20),
@model nvarchar(20),
@capacity nchar(10),
@year_of_manufacture datetime,
@serial_no nvarchar(50),
@attach nvarchar(50),
@siteid nvarchar(50),
@location nvarchar(50),
@omid nvarchar(20),
@smid nchar(20),
@periodic_maintenance_required bit
)   
AS
     INSERT assetdbase
     (
     assetcodeidtxt
     name_of_assettxt,
     unit_notxt,
     manufacturertxt,
     maketxt,
     modeltxt
     capacitytxt,
     year_of_manufacturetxt,
     attachtxt,
     siteidtxt,
     locationtxt,
     smidtxt,
     periodic_maintenance_requiredtxt
    )
     VALUES(
     @assetcodetxt
     @name_of_asset
     @unit_no
     @manufacturer
     @make
     @model
     @capacity
     @year_of_manufacture
     @attach
     @siteid
     @location
     @smid
     @perodic_maintenance_required

     )
    RETURN
GO

Upvotes: 1

Views: 181

Answers (2)

kemiller2002
kemiller2002

Reputation: 115488

Just to complete what other people are saying, here is the code, and I've added logic to make the script re-runnable, so you won't get a creation error with the script after you run it the first time. 

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



USE metroengineeringdatabase
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InsertRecords]') AND type in (N'P', N'PC'))
BEGIN
exec sp_executesql N'CREATE PROCEDURE [dbo].[InsertRecords] AS SELECT ''PROCEDURE Template'''
END
GO


ALTER PROCEDURE dbo.InsertRecords (
@assetcodeId nvarchar(20),
@name_of_asset nvarchar(20),
@unit_no nvarchar(20),
@manufacturer nvarchar(20),
@make nvarchar(20),
@model nvarchar(20),
@capacity nchar(10),
@year_of_manufacture datetime,
@serial_no nvarchar(50),
@attach nvarchar(50),
@siteid nvarchar(50),
@location nvarchar(50),
@omid nvarchar(20),
@smid nchar(20),
@periodic_maintenance_required bit
)   
AS    

INSERT assetdbase
         (
         assetcodeidtxt,
         name_of_assettxt,
         unit_notxt,
         manufacturertxt,
         maketxt,
         modeltxt
         capacitytxt,
         year_of_manufacturetxt,
         attachtxt,
         siteidtxt,
         locationtxt,
         smidtxt,
         periodic_maintenance_requiredtxt
        )
         VALUES(
         @assetcodetxt,
         @name_of_asset,
         @unit_no,
         @manufacturer,
         @make,
         @model,
         @capacity
         @year_of_manufacture,
         @attach,
         @siteid,
         @location,
         @smid,
         @perodic_maintenance_required
         )

Upvotes: 2

Peter Radocchia
Peter Radocchia

Reputation: 11007

You're missing the comma after the first column in the INSERT list.

You're also missing commas between each value in the VALUES list.

Upvotes: 6

Related Questions