INSERT INTO User-define table type

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

INSERT INTO User-define table type

Author

Topic

expat
Starting Member

7 Posts

Posted - 2010-06-21 : 19:26:36

Hi

I have a problem with a stored procedure, and i get error "There are fewer columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement."

The table I try to insert to have IDENTITY. Can someone tell me what I am doing wrong.
Here is the procedure

USE [Amari]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_AddAgentRateSheet](
@Season SeasonType READONLY,
@RateType RateType READONLY,
@PeakSeason PeakSeasonType READONLY,
@Meals MealsType READONLY,
@OtherMeals OtherMealsType READONLY,
@Transfer TransferType READONLY,
@HotelId int,
@ContractYear int,
@RateSheetName nvarchar(150),
@MarketValidityId int,
@RatesTax nvarchar(50),
@RatesMeals nvarchar(50),
@Status bit)
AS

BEGIN

SET NOCOUNT ON;
BEGIN TRANSACTION
DECLARE @SheetId int;
DECLARE @SeasonId int;
DECLARE @PeakSeasonId int;
DECLARE @RateId int;
DECLARE @MealsId int;
DECLARE @OtherMealsId int;
DECLARE @TransferId int;
--DECLARE variables for Season
DECLARE @SeasonName nvarchar(100);
DECLARE @FromDate datetime;
DECLARE @ToDate datetime;
--DECLARE variables for Rate
DECLARE @RoomAlternativeId int;
DECLARE @Rate decimal(18,2);
DECLARE @DayUseRate decimal(18,2);
--DECLARE variables for PeakSeason
DECLARE @PeakSeasonName nvarchar(100);
DECLARE @PeakFromDate datetime;
DECLARE @PeakToDate datetime;
DECLARE @PeakRate decimal(18,2);
--DECLARE variables for meals
DECLARE @ABF decimal(18,2);
DECLARE @Lunch decimal(18,2);
DECLARE @Dinner decimal(18,2);
DECLARE @Galla24 decimal(18,2);
DECLARE @Galla25 decimal(18,2);
DECLARE @Galla31 decimal(18,2);
--DECLARE variables for OtherMeals
DECLARE @Details nvarchar(max);
--DECLARE variables for Transfer
DECLARE @TransferDetails nvarchar(max);

INSERT INTO AgentsRatesSheet
VALUES (@HotelId, @ContractYear, @RateSheetName, @MarketValidityId,
@RatesTax, @RatesMeals, @Status)
SELECT @SheetId = AgentRateSheetId FROM AgentsRatesSheet
WHERE RateSheetName = @RateSheetName
IF @@ERROR <> 0
BEGIN
ROLLBACK
RAISERROR ('Error in inserting into AgentsRatesSheet', 16, 1)
RETURN
END

--Insert into Season
INSERT INTO Season
(RateSheetId, RateId, SeasonName, FromDate, ToDate)
VALUES (@SheetId, '0', @SeasonName, @FromDate, @ToDate)
SELECT @SheetId, '0', SeasonName, FromDate, ToDate FROM @Season

SELECT @SeasonId = SeasonId FROM Season
WHERE SeasonName = @SeasonName
IF @@ERROR <> 0
BEGIN
ROLLBACK
RAISERROR ('Error in inserting into Season', 16, 1)
RETURN
END

--Insert into Rate
INSERT INTO Rate
(SeasonId, RoomAlternativeId, Rate, RateDayUse)
VALUES (@SeasonId, @RoomAlternativeId, @Rate, @DayUseRate)
SELECT @SeasonId, RoomAlternativeId, Rate, RateDayUse FROM @RateType

SELECT @RateId = RateId FROM Rate
WHERE SeasonId = @SeasonId
IF @@ERROR <> 0
BEGIN
ROLLBACK
RAISERROR ('Error in inserting into Rate', 16, 1)
RETURN
END

--Insert into PeakSeason
INSERT INTO PeakSeason
(RateSheetId, PeakSeasonName, FromDate, ToDate, Rate)
VALUES (@SheetId, @PeakSeasonName, @FromDate, @ToDate, @Rate)
SELECT PeakSeasonName, FromDate, ToDate, Rate FROM @PeakSeason

SELECT @PeakSeasonId = PeakSeasonId FROM PeakSeason
WHERE RateSheetId = @SheetId
IF @@ERROR <> 0
BEGIN
ROLLBACK
RAISERROR ('Error in inserting into Rate', 16, 1)
RETURN
END

--Insert into Meals
INSERT INTO Meals
(RateSheetId, ABF, Dinner, Galla24, Galla25, Galla31)
VALUES (@SheetId, @ABF, @Lunch, @Dinner, @Galla24, @Galla25, @Galla31)
SELECT ABF, Lunch, Dinner, Galla24, Galla25, Galla31 FROM @Meals

SELECT @MealsId = MealsId FROM Meals
WHERE RateSheetId = @SheetId
IF @@ERROR <> 0
BEGIN
ROLLBACK
RAISERROR ('Error in inserting into Meals', 16, 1)
RETURN
END

--Insert into OtherMeals
INSERT INTO OtherMeals
(RateSheetId, Details)
VALUES (@SheetId, @Details)
SELECT Details FROM @OtherMeals

SELECT @OtherMealsId = OtherMealsId FROM OtherMeals
WHERE RateSheetId = @SheetId
IF @@ERROR <> 0
BEGIN
ROLLBACK
RAISERROR ('Error in inserting into OtherMeals', 16, 1)
RETURN
END

--Insert into Transfer
INSERT INTO [Transfer]
(RateSheetId, TransferDetails)
VALUES (@SheetId, @TransferDetails)
SELECT Details FROM @Transfer

SELECT @TransferId = TransferId FROM Transfer
WHERE RateSheetId = @SheetId
IF @@ERROR <> 0
BEGIN
ROLLBACK
RAISERROR ('Error in inserting into Transfer', 16, 1)
RETURN
END

--Updatating Season
UPDATE Season
SET RateId = @RateId
WHERE SeasonName = @SeasonName
IF @@ERROR <> 0
BEGIN
ROLLBACK
RAISERROR ('Error in updating into Season', 16, 1)
RETURN
END

--Inserting into AgentRateSheetIndex
INSERT INTO AgentRateSheetIndex
VALUES (@SheetId, @RateId, @PeakSeasonId, @MealsId, @OtherMealsId, @TransferId)
BEGIN
ROLLBACK
RAISERROR ('Error in inserting into AgentRateSheetIndex', 16, 1)
RETURN
END

COMMIT
END
GO

In the INSERT INTO I have tried without VALUES to

Regards Rune

expat
Starting Member

7 Posts

Posted - 2010-06-21 : 21:07:28

I found out of it. Here is the correct procedure:

USE [Amari]
GO

/****** Object: StoredProcedure [dbo].[sp_AddAgentRateSheet] Script Date: 06/22/2010 08:05:16 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_AddAgentRateSheet](
@Season SeasonType READONLY,
@RateType RateType READONLY,
@PeakSeason PeakSeasonType READONLY,
@Meals MealsType READONLY,
@OtherMeals OtherMealsType READONLY,
@Transfer TransferType READONLY,
@HotelId int,
@ContractYear int,
@RateSheetName nvarchar(150),
@MarketValidityId int,
@RatesTax nvarchar(50),
@RatesMeals nvarchar(50),
@Status bit)
AS

BEGIN

SET NOCOUNT ON;
BEGIN TRANSACTION
DECLARE @SheetId int;
DECLARE @SeasonId int;
DECLARE @PeakSeasonId int;
DECLARE @RateId int;
DECLARE @MealsId int;
DECLARE @OtherMealsId int;
DECLARE @TransferId int;
--DECLARE variables for Season
DECLARE @SeasonName nvarchar(100);
DECLARE @FromDate datetime;
DECLARE @ToDate datetime;
--DECLARE variables for Rate
DECLARE @RoomAlternativeId int;
DECLARE @Rate decimal(18,2);
DECLARE @DayUseRate decimal(18,2);
--DECLARE variables for PeakSeason
DECLARE @PeakSeasonName nvarchar(100);
DECLARE @PeakFromDate datetime;
DECLARE @PeakToDate datetime;
DECLARE @PeakRate decimal(18,2);
--DECLARE variables for meals
DECLARE @ABF decimal(18,2);
DECLARE @Lunch decimal(18,2);
DECLARE @Dinner decimal(18,2);
DECLARE @Galla24 decimal(18,2);
DECLARE @Galla25 decimal(18,2);
DECLARE @Galla31 decimal(18,2);
--DECLARE variables for OtherMeals
DECLARE @Details nvarchar(max);
--DECLARE variables for Transfer
DECLARE @TransferDetails nvarchar(max);

--SET IDENTITY_INSERT AgentsRatesSheet ON
INSERT INTO AgentsRatesSheet
VALUES (@HotelId, @ContractYear, @RateSheetName, @MarketValidityId,
@RatesTax, @RatesMeals, @Status)
--SELECT @SheetId = AgentRateSheetId FROM AgentsRatesSheet
--WHERE RateSheetName = @RateSheetName
SELECT @SheetId = SCOPE_IDENTITY()

IF @@ERROR <> 0
BEGIN
ROLLBACK
RAISERROR ('Error in inserting into AgentsRatesSheet', 16, 1)
RETURN
END

--Insert into Season
--SET IDENTITY_INSERT Season ON
INSERT INTO Season
(RateSheetId, RateId, SeasonName, FromDate, ToDate)
SELECT @SheetId, '0', SeasonName, FromDate, ToDate FROM @Season
SELECT @SeasonId = SCOPE_IDENTITY()
IF @@ERROR <> 0
BEGIN
ROLLBACK
RAISERROR ('Error in inserting into Season', 16, 1)
RETURN
END

--Insert into Rate
--SET IDENTITY_INSERT Rate ON
INSERT INTO Rate
(SeasonId, RoomAlternativeId, Rate, RateDayUse)
SELECT @SeasonId, RoomAlternativeId, Rate, RateDayUse FROM @RateType
--SET IDENTITY_INSERT Rate OFF
SELECT @RateId = SCOPE_IDENTITY()
IF @@ERROR <> 0
BEGIN
ROLLBACK
RAISERROR ('Error in inserting into Rate', 16, 1)
RETURN
END

--Insert into PeakSeason
--SET IDENTITY_INSERT PeakSeason ON
INSERT INTO PeakSeason
(PeakSeasonName, FromDate, ToDate, Rate)
SELECT PeakSeasonName, FromDate, ToDate, Rate FROM @PeakSeason
--SET IDENTITY_INSERT PeakSeason OFF
SELECT @PeakSeasonId = SCOPE_IDENTITY()
IF @@ERROR <> 0
BEGIN
ROLLBACK
RAISERROR ('Error in inserting into Rate', 16, 1)
RETURN
END

--Insert into Meals
--SET IDENTITY_INSERT Meals ON
INSERT INTO Meals
(RateSheetId, ABF, Dinner, Galla24, Galla25, Galla31)
SELECT ABF, Lunch, Dinner, Galla24, Galla25, Galla31 FROM @Meals
--SET IDENTITY_INSERT Melas OFF
SELECT @MealsId = SCOPE_IDENTITY()

IF @@ERROR <> 0
BEGIN
ROLLBACK
RAISERROR ('Error in inserting into Meals', 16, 1)
RETURN
END

--Insert into OtherMeals
--SET IDENTITY_INSERT OtherMeals ON
INSERT INTO OtherMeals
(Details)
SELECT Details FROM @OtherMeals
--SET IDENTITY_INSERT OtherMeals OFF
SELECT @OtherMealsId = SCOPE_IDENTITY()
IF @@ERROR <> 0
BEGIN
ROLLBACK
RAISERROR ('Error in inserting into OtherMeals', 16, 1)
RETURN
END

--Insert into Transfer
--SET IDENTITY_INSERT [Transfer] ON
INSERT INTO [Transfer]
(TransferDetails)
SELECT Details FROM @Transfer

SELECT @TransferId = SCOPE_IDENTITY()
IF @@ERROR <> 0
BEGIN
ROLLBACK
RAISERROR ('Error in inserting into Transfer', 16, 1)
RETURN
END

--Updatating Season
UPDATE Season
SET RateId = @RateId
WHERE SeasonName = @SeasonName
IF @@ERROR <> 0
BEGIN
ROLLBACK
RAISERROR ('Error in updating into Season', 16, 1)
RETURN
END

--Inserting into AgentRateSheetIndex
--SET IDENTITY_INSERT AgentRateSheetIndex ON
INSERT INTO AgentRateSheetIndex
VALUES (@SheetId, @SeasonId, @RateId, @PeakSeasonId, @MealsId, @OtherMealsId, @TransferId)
BEGIN
ROLLBACK
RAISERROR ('Error in inserting into AgentRateSheetIndex', 16, 1)
RETURN
END
COMMIT
END

GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources