July 11, 2013 at 3:22 am
Hai friends,
I ve the table like
create table travel_request
(
request_id int identity primarykey,
travel_purpose varchar(10),
total_amount varchar(10)
)
and one more table is
create table onward_journey
(
onward_journey_id int primarykey,
request_id int foreignkey references travel_request(request_id),
departuredate datetime,
from_place varchar(10),
to_place varchar(10)
)
in the table of travel_request insertion is one page of my web appllication,
and i wanna catch that request_id pass through onward_joureny table so i made the query like:
alter procedure Insert_Journey
(
@departuredate datetime,
@from_location varchar(50),
@to_location varchar(50),
@metro nvarchar(50),
@trans_all nvarchar(50),
@mode_of_travel nvarchar(50),
@seat_type nvarchar(50),
@no_of_days int,
@other_details varchar(50),
@status_id int,
@request int output
)
as
BEGIN
DECLARE @MaxDate datetime
SELECT @MaxDate = MAX(DepartureDate) FROM onward_journey where request_id=@request
SELECT @request=IDENT_CURRENT('travel_request ')
IF(@MaxDate > @departuredate)
begin
RAISERROR('Your error message for departuredate should be greater then maxdate',16,1)
RETURN
END
insert into onward_journey(departuredate,from_location,to_location,metro,trans_all,mode_of_travel,seat_type,no_of_days,other_details,status_id) values(@departuredate,@from_location,@to_location,@metro,@trans_all,@mode_of_travel,@seat_type,@no_of_days,@other_details,'2')
set @request=IDENT_CURRENT ('travel_request ')
return @request
end
but its showing null value on my request id......... guide me?
July 11, 2013 at 3:40 am
This doesn't make sense. Your INSERT statement doesn't fit with the definition for the onward_journey table.
John
July 11, 2013 at 7:39 am
insert into onward_journey(departuredate,from_location,to_location,metro,trans_all,mode_of_travel,seat_type,no_of_days,other_details,status_id) values(@departuredate,@from_location,@to_location,@metro,@trans_all,@mode_of_travel,@seat_type,@no_of_days,@other_details,'2')
set @request=IDENT_CURRENT ('travel_request ')
return @request
You have a couple of issues going on here.
First you are using IDENT_CURRENT. That will get the last identity value for that table on ANY connection. In other words, concurrency is blown out the window here because it is not necessarily the one you just inserted. You should instead use SCOPE_IDENTITY. It will get the last identity value for the current connection.
Take a look at this article that does a nice job explaining the differences. http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/[/url]
The bigger issue is that you are looking for the identity value of a different table. You insert into onward_journey and then try to get the IDENT_CURRENT for travel_request. That will most likely be NULL most of the time unless there is another active connection that has inserted into that table.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply