August 27, 2009 at 6:12 am
Hi all,
This is my first post here. Hope to have answers for me.
I am a newbie to SQL. I have to write a stored procedure for calculating rows and then inserting those values in Table called Locations. Here is what i need to do:-
I have 5 columns A,B,C,D,E. Max length for each column is 80 feet and then i divided columns with rows. Here each cell will be 8 feet long. So logically there must be (80/8)10 rows for each column. So i can divide A column into A1,A2,A3,A4,A5,A6,A7,A8,A9,A10.And now i have to add these rows into Location table. Here is what I am trying to do:-
CREATE PROCEDURE sp_FillLocation
(
@maxlength int,
@length int
)
as
declare @i int
declare @count int
set @count = @maxlength/@length
while @i <= @count
begin
insert into Location values('A' '' +@i '',0)
set @i = @i + 1
end
GO
It is giving me errors! And I am not sure if this approach is right or wrong. I have to add rows from A to E with the help of some loop so please can anyone help me here!
August 27, 2009 at 6:22 am
It's quite hard to answer your question, mainly because it's hard to understand.
If you want a quicker answer, please provide the create table statement for Locations, some sample data and the error you are getting from your code.
Take a look at this article, it will help you providing DDL and sample data:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
I'll be glad to help as soon as I understand your question.
Regards
Gianluca
-- Gianluca Sartori
August 27, 2009 at 6:36 am
We have a location grid here. For that We created a locations table.
Locations table has two columns :-
Location(varchar(5))
Filled (Boolean)
Now I need to fill this Locations table with data that i have to generate from stored procedure using mathematical equation! The grid that we have has 5 rows A,B,C,D,E. Maximum length of each row is provided that is 80 feet. now I have to place carts in these rows and width of cart is 8 feet. so logically in each row say A , i can place 10 carts (80/8). So i can divide each row in 10 columns A1 to A10,b1 t0 B10 , C1 to C10 , D1 to D10, E1 to E10.
so now Data for Locations table will be:-
Location Filled
A1 0
A2 0
and so on upto E10
I have no experience in SQL so i tried to write following code:-
CREATE PROCEDURE sp_FillLocation
(
@maxlength int,
@length int
)
as
declare @i int
declare @count int
set @count = @maxlength/@length
set @i=1
while @i <= @count
begin
insert into Location values('A' + @i ,0)
set @i = @i + 1
end
GO
This is working fine!
This stored procedure adds values from A1 to A10 into Locations table. I just need to know logic for adding other values i.e. B1 t0 B10 , C1 to C10 and so on
August 27, 2009 at 7:07 am
This should do the trick:
ALTER PROCEDURE sp_FillLocation
(
@maxlength INT,
@length INT
)
AS
DECLARE @i INT
DECLARE @j-2 INT
DECLARE @count INT
SET @count = @maxlength / @length
SET @j-2 = 65
WHILE @j-2 <= 69
BEGIN
SET @i = 1
WHILE @i <= @count
BEGIN
INSERT INTO Location
VALUES (CHAR(@j) + CAST(@i AS VARCHAR(2)), 0 )
SET @i = @i + 1
END
END
Regards
Gianluca
-- Gianluca Sartori
August 27, 2009 at 7:21 am
Thank you very much!!!! You saved me!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply