SQL Variable as an INSERT INTO Value

Question

All I'm trying to do is declare a variable called @ReservationID, initialize it to a value from the Reservation table where certain conditions exist, and use that value as a parameter to insert a new Folio table entry. When I go to print my Folio table, the new table entry doesn't exist. I've checked and ran the SELECT statement, and it comes up with the correct value based on the conditions. Am I not initializing or passing the variable correctly? What am I missing here? Any help would be appreciated.

DECLARE @ReservationID smallint;
SET @ReservationID = (SELECT ReservationID FROM Reservation WHERE ReservationDate = CONVERT(varchar, GETDATE()) AND CreditCardID = 1)

SET IDENTITY_INSERT Folio OFF
INSERT INTO Folio (ReservationID, GuestID, RoomID, QuotedRate, CheckinDate, Nights, Status, Comments, DiscountID)
VALUES(@ReservationID, 500, 20, 35.50, '07/24/2016', 3, 'R', NULL, 1);

In the results' messages tab what no of rows affected u get? — Nikhil Vartak
– Nikhil Vartak, Commented Jul 16, 2016 at 20:33
Code looks correct. Only requirement for @ReservationID is that subquery must return only one value (you may use top(1) for this). Is ReservationID is IDENTITY column? — Aleksey Ratnikov
– Aleksey Ratnikov, Commented Jul 16, 2016 at 20:53

alphacoder · Accepted Answer · 2016-07-16 22:48:56Z

1

You may not be initializing or passing the variable correctly.

Refer MSDN Here for assigning a variable to the value of an expression.

You need to correct the SET & IDENTITY_INSERT as below,

DECLARE @ReservationID smallint;
select @ReservationID = ReservationID FROM Reservation 
WHERE ReservationDate = CONVERT(varchar, GETDATE()) AND CreditCardID = 1

SET IDENTITY_INSERT Folio ON
INSERT INTO Folio (ReservationID, GuestID, RoomID, QuotedRate,  CheckinDate,Nights, Status, Comments, DiscountID)
VALUES(@ReservationID, 500, 20, 35.50, '07/24/2016', 3, 'R', NULL, 1);

Below is my quick test code,

create table Reservation(ReservationID smallint identity, CreditCardID int) 

insert into Reservation values(1),(2),(3),(4),(5)

SET IDENTITY_INSERT Reservation ON

DECLARE @ReservationID smallint;

select @ReservationID =  ReservationID FROM Reservation

insert into Reservation (ReservationID, CreditCardID)
values(@ReservationID,100)

edited Jul 16, 2016 at 22:48

answered Jul 16, 2016 at 20:33

alphacoder

5832 gold badges9 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Nikhil Vartak Over a year ago

In fact u introduced syntax error above with that ) after 1. If it was syntax problem SSMS would state so. The SET syntax is correct that OP has used. That is not a problem. What u showed is just another syntax.

alphacoder Over a year ago

Hi @Nikhil Vartak, There is a sytax difference for assigning a value to a variable and assigning expression value to a variable . Please refer link.

Nikhil Vartak Over a year ago

True and I know that. I meant using select @variable vs set @variable = (select) is not a cause of issue here because OP is able to get a proper @variable value.

Shiva Over a year ago

The OP has history of not picking any answers for any of his several questions. See his/her profile. stackoverflow.com/users/6477547/amaidie90?tab=questions I think it's a waste of time answering his/her questions.

Collectives™ on Stack Overflow

SQL Variable as an INSERT INTO Value

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related