Thursday, July 11, 2013

[T-SQL] How to add IDENT_CURRENT for foreignkey in procedure?

[T-SQL] How to add IDENT_CURRENT for foreignkey in procedure?


How to add IDENT_CURRENT for foreignkey in procedure?

Posted: 10 Jul 2013 08:22 PM PDT

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 iscreate 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 endbut its showing null value on my request id......... guide me?

Store Procedure running slow

Posted: 10 Jul 2013 09:43 AM PDT

Hi Experts,I have a Store Procedure which is running slow in Prod but running fine in Stage. I have rebuild the index and updates Statistics but there was no difference in Prod.When I am passing the 'H' Value in Store Procedure its taking 1 Hour and 30 min in Prod and in Stage its taking 11 min I checked the Server configuration Stage is running on 3 Ghz and Prod is 2 Ghz I don't its a Server Problem we moved from a 4.71 environment to a 5.01 environment but nothing was changed in DBs I am attaching the store proc if anyone can help I will appreciate it . Thanks

Check series is valid or not

Posted: 10 Jul 2013 09:12 PM PDT

Hi, I have a scenario in which I have to check that whether I am correct no of series or not...[code="sql"]Create table tbl( ID int identity,number varchar(10),numstatus varchar(10))INSERT INTO tblValues('V001','Active'),('V002','Active'),('V003','Active'),('V004','InActive'),('V005','Active')[/code]I tried in this manner....[code="sql"]Declare @firstno varchar(10) = 'V0001', @scndno varchar(10) = 'V0005'Create table #voucherno( voucherno varchar(10) )INSERT INTO #vouchernoSELECT VoucherNo from tbl WHERE number between @firstno and @scndno SELECT gv.number FROM tbl As gv INNER JOIN #voucherno ro ON ro.VoucherNo = gv.number WHERE gv.numstatus = 'Active'[/code]AS values that i passed in parameters @frstno and @scndno is not in series as v004 is inactive so I should get message invalid series....My query gives me output as V001,v002,V003,v005. I tried with IF EXISTS but didnt get desired output

Help on Date Time Validation

Posted: 10 Jul 2013 06:39 AM PDT

HelloCan someone please share if there are any functions or code to validate the Date in different format settings?Ex:----If I enter the value "9999", the function should validate against the Date Formats ("MM/DD/YYYY", "DD-MMM-YYYY") and return 0, because 9999 is not a valid DateIf I enter the value "01-Jan-9999", the function should validate against the Date Formats ("MM/DD/YYYY", "DD-MMM-YYYY") and return 1.Also is there any common function available to use for similar kind of different requirements?ThanksShuaib

No comments:

Post a Comment

Search This Blog