August 7, 2013 at 9:00 am
Hai friends ,
i ve the table like
travel_request
create table travel_request
(
request_id int identity,
user_id varchar(20),
travel_purpose varchar(20)
)
insert into travel_request (user_id,travel_purpose) values ('012','Market Visit')
journey
create table journey
(
request_id int references travel_request(request_id),
departuredate datetime,
from varchar(20),
to varchar(20)
)
now my requirement is when user enter the date if it is table ll show already exists........
how to make a code?
August 7, 2013 at 9:10 am
raghuldrag (8/7/2013)
Hai friends ,i ve the table like
travel_request
create table travel_request
(
request_id int identity,
user_id varchar(20),
travel_purpose varchar(20)
)
insert into travel_request (user_id,travel_purpose) values ('012','Market Visit')
journey
create table journey
(
request_id int references travel_request(request_id),
departuredate datetime,
from varchar(20),
to varchar(20)
)
now my requirement is when user enter the date if it is table ll show already exists........
how to make a code?
If you have a sp then you can simply write a sql statement to check if the user already entered the record or not like this:
IF EXISTS(select 1 from travel_request tr inner join journey jr on tr.request_id = jr.request_id where tr.user_id = @userid and jr.departuredate = @Date) BEGIN
Return validationerrorcode;
END
August 7, 2013 at 9:17 am
like above my requirement is these procedure correct ah?
alter procedure save_draft
(
@username varchar(20),
@departure datetime,
@request int,
@error Varchar(100)=null output
)
as
begin
declare @userid varchar(20)
select @userid=user_id from users where username=@username
if exists(select a.departuredate from onward_journey a inner join travel_request b on a.request_id=b.request_id where b.user_id=@userid and a.departuredate=@departure)
BEGIN
SET @error='Already Exists Same Date'
END
ELSE
BEGIN
update travel_request set active_tag='Y' where request_id=@request
SET @error='Record Saved Successfully'
END
SELECT @error as error
end
August 7, 2013 at 9:29 am
raghuldrag (8/7/2013)
like above my requirement is these procedure correct ah?alter procedure save_draft
(
@username varchar(20),
@departure datetime,
@request int,
@error Varchar(100)=null output
)
as
begin
declare @userid varchar(20)
select @userid=user_id from users where username=@username
if exists(select a.departuredate from onward_journey a inner join travel_request b on a.request_id=b.request_id where b.user_id=@userid and a.departuredate=@departure)
BEGIN
SET @error='Already Exists Same Date'
END
ELSE
BEGIN
update travel_request set active_tag='Y' where request_id=@request
SET @error='Record Saved Successfully'
END
SELECT @error as error
end
I would use an integer as a return variable. Then for each error you can start with -1,-2... etc and for success, you can return 0.
August 7, 2013 at 11:29 am
Why not just raise an error when there is an error?
http://technet.microsoft.com/en-us/library/ms178592.aspx
_______________________________________________________________
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 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply