February 8, 2010 at 7:13 am
I have a biggggg problem!!! i have a webpage have gridview, the button in gridview allocates locations to an order, if the location is not selected by the dropdownlist in gridview. I am using stored procedure for automatic allocation of locations. Here is the problem, i dont know where the problem is but the program allocates same location to different order which should not be happening. I could not understand where i am wrong so thought to take advice of all the experts!!! please help me this is very important for me!
Table Name:- Location
Id - int
location - varchar(15)
filled- bit
stored procedure:-CREATE PROCEDURE [dbo].[sp_GetLocations]
(
@loc_required int
)
AS
BEGIN
declare @gaps table (id int identity(1, 1),Lct varchar(10), locid int)
insert into @gaps(Lct, locid)
select location, id from location where filled =0 order by id
declare @templocation table(from_cell varchar(10),to_cell varchar(10),no_of_free_cells int)
insert into @templocation
select g1.Lct as 'from cell', g2.Lct as 'to cell' , [number of free cells] from
(select min(locid) as 'from_cell' , max (locid) 'to_cell' , count(Lct) as 'number of free cells' from @gaps
group by left(lct, 1), locid-id ) x
inner join @gaps g1 on x.from_cell = g1.locid
inner join @gaps g2 on x.to_cell = g2.locid
declare @finallocation table(from_cell varchar(10),to_cell varchar(10),no_of_free_cells int)
insert into @finallocation
select * from @templocation where no_of_free_cells >= @loc_required order by no_of_free_cells,from_cell
select top 1 * from @finallocation
END
If this logic is wrong , please help me in figuring out new logic.Thanks
February 10, 2010 at 2:15 pm
Is the order that it is returning from what you can tell based on the id column, or is it showing up based on something else, like the location column?
Joie Andrew
"Since 1982"
February 10, 2010 at 3:10 pm
First off, you don't need to use table variables to make this work. Second, it's a bit difficult to help you on this without some sample data and an example of the result set you wish the SP to produce.
Please follow the link in my signature line for instructions on how to post your sample data so we can help you.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply