I/O error

Hello,
I am getting following while creating a non-clustered
index on a table having 20 million records. Can anyone
suggest a solution for this...
Server: Msg 823, Level 24, State 11, Line 1
I/O error 33(error not found) detected during write at
offset 0x000002c8450000 in
file 'F:\SQL2K_Data\PROD_Data.MDF'.
Connection Broken
Regards,
ManojManoj,
This error came from the Operating system and not SQL Server. It
indicates a problem with your underlying hardware, most probably your
disk subsystem. Can you reproduce it on another server?
Run sqlhdtst available from http://support.microsoft.com/?id=178444 to
reproduce the error on your hardware.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Manoj Raheja wrote:
> Hello,
> I am getting following while creating a non-clustered
> index on a table having 20 million records. Can anyone
> suggest a solution for this...
> Server: Msg 823, Level 24, State 11, Line 1
> I/O error 33(error not found) detected during write at
> offset 0x000002c8450000 in
> file 'F:\SQL2K_Data\PROD_Data.MDF'.
> Connection Broken
> Regards,
> Manoj
>|||Manoj
It seems to be serious problem.
I suggest to restore database from the last BACKUP or if you don't have then
from query Analyser, master database, execute
dbcc checkdb(<databas_name> )
"Manoj Raheja" <manoj_raheja@.hotmail.com> wrote in message
news:1bfe301c451ed$eaba6680$a101280a@.phx
.gbl...
> Hello,
> I am getting following while creating a non-clustered
> index on a table having 20 million records. Can anyone
> suggest a solution for this...
> Server: Msg 823, Level 24, State 11, Line 1
> I/O error 33(error not found) detected during write at
> offset 0x000002c8450000 in
> file 'F:\SQL2K_Data\PROD_Data.MDF'.
> Connection Broken
> Regards,
> Manoj
>

I/O error

Hello,
I am getting following while creating a non-clustered
index on a table having 20 million records. Can anyone
suggest a solution for this...
Server: Msg 823, Level 24, State 11, Line 1
I/O error 33(error not found) detected during write at
offset 0x000002c8450000 in
file 'F:\SQL2K_Data\PROD_Data.MDF'.
Connection Broken
Regards,
Manoj
Manoj,
This error came from the Operating system and not SQL Server. It
indicates a problem with your underlying hardware, most probably your
disk subsystem. Can you reproduce it on another server?
Run sqlhdtst available from http://support.microsoft.com/?id=178444 to
reproduce the error on your hardware.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Manoj Raheja wrote:
> Hello,
> I am getting following while creating a non-clustered
> index on a table having 20 million records. Can anyone
> suggest a solution for this...
> Server: Msg 823, Level 24, State 11, Line 1
> I/O error 33(error not found) detected during write at
> offset 0x000002c8450000 in
> file 'F:\SQL2K_Data\PROD_Data.MDF'.
> Connection Broken
> Regards,
> Manoj
>
|||Manoj
It seems to be serious problem.
I suggest to restore database from the last BACKUP or if you don't have then
from query Analyser, master database, execute
dbcc checkdb(<databas_name>)
"Manoj Raheja" <manoj_raheja@.hotmail.com> wrote in message
news:1bfe301c451ed$eaba6680$a101280a@.phx.gbl...
> Hello,
> I am getting following while creating a non-clustered
> index on a table having 20 million records. Can anyone
> suggest a solution for this...
> Server: Msg 823, Level 24, State 11, Line 1
> I/O error 33(error not found) detected during write at
> offset 0x000002c8450000 in
> file 'F:\SQL2K_Data\PROD_Data.MDF'.
> Connection Broken
> Regards,
> Manoj
>
sql

I/O error

Hello,
I am getting following while creating a non-clustered
index on a table having 20 million records. Can anyone
suggest a solution for this...
Server: Msg 823, Level 24, State 11, Line 1
I/O error 33(error not found) detected during write at
offset 0x000002c8450000 in
file 'F:\SQL2K_Data\PROD_Data.MDF'.
Connection Broken
Regards,
ManojManoj,
This error came from the Operating system and not SQL Server. It
indicates a problem with your underlying hardware, most probably your
disk subsystem. Can you reproduce it on another server?
Run sqlhdtst available from http://support.microsoft.com/?id=178444 to
reproduce the error on your hardware.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Manoj Raheja wrote:
> Hello,
> I am getting following while creating a non-clustered
> index on a table having 20 million records. Can anyone
> suggest a solution for this...
> Server: Msg 823, Level 24, State 11, Line 1
> I/O error 33(error not found) detected during write at
> offset 0x000002c8450000 in
> file 'F:\SQL2K_Data\PROD_Data.MDF'.
> Connection Broken
> Regards,
> Manoj
>|||Manoj
It seems to be serious problem.
I suggest to restore database from the last BACKUP or if you don't have then
from query Analyser, master database, execute
dbcc checkdb(<databas_name>)
"Manoj Raheja" <manoj_raheja@.hotmail.com> wrote in message
news:1bfe301c451ed$eaba6680$a101280a@.phx.gbl...
> Hello,
> I am getting following while creating a non-clustered
> index on a table having 20 million records. Can anyone
> suggest a solution for this...
> Server: Msg 823, Level 24, State 11, Line 1
> I/O error 33(error not found) detected during write at
> offset 0x000002c8450000 in
> file 'F:\SQL2K_Data\PROD_Data.MDF'.
> Connection Broken
> Regards,
> Manoj
>

I would like to generate the combinations of the excel function combin in t-sql

so if I did combin(35,3) the result would be 6545 and the combinations are
listed below. I know how to do it by creating three nested loops in c# but I
think there's a way to do it in sql by using a cross join or self join but I
don't see it.
Thanks
1 2 3
1 2 4
1 2 5
1 2 6
1 2 7
1 2 8
1 2 9
1 2 10
1 2 11
1 2 12
1 2 13
1 2 14
1 2 15
1 2 16
1 2 17
1 2 18
1 2 19
1 2 20
1 2 21
1 2 22
1 2 23
1 2 24
1 2 25
1 2 26
1 2 27
1 2 28
1 2 29
1 2 30
1 2 31
1 2 32
1 2 33
1 2 34
1 2 35
1 3 4
1 3 5
1 3 6
1 3 7
1 3 8
1 3 9
1 3 10
1 3 11
1 3 12
1 3 13
1 3 14
1 3 15
1 3 16
1 3 17
1 3 18
1 3 19
1 3 20
1 3 21
1 3 22
1 3 23
1 3 24
1 3 25
1 3 26
1 3 27
1 3 28
1 3 29
1 3 30
1 3 31
1 3 32
1 3 33
1 3 34
1 3 35
1 4 5
Create table a ( id int not null)
declare @.a int
select @. = 1
While @.a <= 35
begin
insert into a values (@.a)
select @.a = @.a + 1
end
select a.id, b.id, c.id from
a inner join a as b on a.id = b.id
inner join c on a.id = c.id
hope this is what you are looking for
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"D" <Dave@.nothing.net> wrote in message
news:u4$Snm0RFHA.1172@.TK2MSFTNGP12.phx.gbl...
> so if I did combin(35,3) the result would be 6545 and the combinations are
> listed below. I know how to do it by creating three nested loops in c# but
I
> think there's a way to do it in sql by using a cross join or self join but
I
> don't see it.
> Thanks
>
> 1 2 3
> 1 2 4
> 1 2 5
> 1 2 6
> 1 2 7
> 1 2 8
> 1 2 9
> 1 2 10
> 1 2 11
> 1 2 12
> 1 2 13
> 1 2 14
> 1 2 15
> 1 2 16
> 1 2 17
> 1 2 18
> 1 2 19
> 1 2 20
> 1 2 21
> 1 2 22
> 1 2 23
> 1 2 24
> 1 2 25
> 1 2 26
> 1 2 27
> 1 2 28
> 1 2 29
> 1 2 30
> 1 2 31
> 1 2 32
> 1 2 33
> 1 2 34
> 1 2 35
> 1 3 4
> 1 3 5
> 1 3 6
> 1 3 7
> 1 3 8
> 1 3 9
> 1 3 10
> 1 3 11
> 1 3 12
> 1 3 13
> 1 3 14
> 1 3 15
> 1 3 16
> 1 3 17
> 1 3 18
> 1 3 19
> 1 3 20
> 1 3 21
> 1 3 22
> 1 3 23
> 1 3 24
> 1 3 25
> 1 3 26
> 1 3 27
> 1 3 28
> 1 3 29
> 1 3 30
> 1 3 31
> 1 3 32
> 1 3 33
> 1 3 34
> 1 3 35
> 1 4 5
>
|||Thanks. There were a couple I think just typo syntax errors but once correct
it produced this
111
222
333
444
...
I found another sample and was able to modify that to get the results I
wanted, it went like this
CREATE TABLE Elements (i INTEGER NOT NULL);
declare @.index int
set @.index = 1
while @.index <= 10
begin
INSERT INTO Elements VALUES (@.index);
set @.index = @.index + 1
end
SELECT E1.i A, E2.i B , E3.i C
FROM Elements AS E1, Elements AS E2, Elements AS E3
WHERE E1.i NOT IN (E2.i, E3.i)
AND E2.i NOT IN (E1.i, E3.i )
AND E3.i NOT IN (E1.i, E2.i)
and e2.i > e1.i and e3.i > e2.i
which did the trick. Thanks for your thoughts though.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:OrowGe2RFHA.2964@.TK2MSFTNGP15.phx.gbl...
> Create table a ( id int not null)
> declare @.a int
> select @. = 1
> While @.a <= 35
> begin
> insert into a values (@.a)
> select @.a = @.a + 1
> end
> select a.id, b.id, c.id from
> a inner join a as b on a.id = b.id
> inner join c on a.id = c.id
>
> hope this is what you are looking for
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "D" <Dave@.nothing.net> wrote in message
> news:u4$Snm0RFHA.1172@.TK2MSFTNGP12.phx.gbl...
> I
> I
>

I would like to generate the combinations of the excel function combin in t-sql

so if I did combin(35,3) the result would be 6545 and the combinations are
listed below. I know how to do it by creating three nested loops in c# but I
think there's a way to do it in sql by using a cross join or self join but I
don't see it.
Thanks
1 2 3
1 2 4
1 2 5
1 2 6
1 2 7
1 2 8
1 2 9
1 2 10
1 2 11
1 2 12
1 2 13
1 2 14
1 2 15
1 2 16
1 2 17
1 2 18
1 2 19
1 2 20
1 2 21
1 2 22
1 2 23
1 2 24
1 2 25
1 2 26
1 2 27
1 2 28
1 2 29
1 2 30
1 2 31
1 2 32
1 2 33
1 2 34
1 2 35
1 3 4
1 3 5
1 3 6
1 3 7
1 3 8
1 3 9
1 3 10
1 3 11
1 3 12
1 3 13
1 3 14
1 3 15
1 3 16
1 3 17
1 3 18
1 3 19
1 3 20
1 3 21
1 3 22
1 3 23
1 3 24
1 3 25
1 3 26
1 3 27
1 3 28
1 3 29
1 3 30
1 3 31
1 3 32
1 3 33
1 3 34
1 3 35
1 4 5Create table a ( id int not null)
declare @.a int
select @. = 1
While @.a <= 35
begin
insert into a values (@.a)
select @.a = @.a + 1
end
select a.id, b.id, c.id from
a inner join a as b on a.id = b.id
inner join c on a.id = c.id
hope this is what you are looking for
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"D" <Dave@.nothing.net> wrote in message
news:u4$Snm0RFHA.1172@.TK2MSFTNGP12.phx.gbl...
> so if I did combin(35,3) the result would be 6545 and the combinations are
> listed below. I know how to do it by creating three nested loops in c# but
I
> think there's a way to do it in sql by using a cross join or self join but
I
> don't see it.
> Thanks
>
> 1 2 3
> 1 2 4
> 1 2 5
> 1 2 6
> 1 2 7
> 1 2 8
> 1 2 9
> 1 2 10
> 1 2 11
> 1 2 12
> 1 2 13
> 1 2 14
> 1 2 15
> 1 2 16
> 1 2 17
> 1 2 18
> 1 2 19
> 1 2 20
> 1 2 21
> 1 2 22
> 1 2 23
> 1 2 24
> 1 2 25
> 1 2 26
> 1 2 27
> 1 2 28
> 1 2 29
> 1 2 30
> 1 2 31
> 1 2 32
> 1 2 33
> 1 2 34
> 1 2 35
> 1 3 4
> 1 3 5
> 1 3 6
> 1 3 7
> 1 3 8
> 1 3 9
> 1 3 10
> 1 3 11
> 1 3 12
> 1 3 13
> 1 3 14
> 1 3 15
> 1 3 16
> 1 3 17
> 1 3 18
> 1 3 19
> 1 3 20
> 1 3 21
> 1 3 22
> 1 3 23
> 1 3 24
> 1 3 25
> 1 3 26
> 1 3 27
> 1 3 28
> 1 3 29
> 1 3 30
> 1 3 31
> 1 3 32
> 1 3 33
> 1 3 34
> 1 3 35
> 1 4 5
>|||Thanks. There were a couple I think just typo syntax errors but once correct
it produced this
111
222
333
444
...
I found another sample and was able to modify that to get the results I
wanted, it went like this
CREATE TABLE Elements (i INTEGER NOT NULL);
declare @.index int
set @.index = 1
while @.index <= 10
begin
INSERT INTO Elements VALUES (@.index);
set @.index = @.index + 1
end
SELECT E1.i A, E2.i B , E3.i C
FROM Elements AS E1, Elements AS E2, Elements AS E3
WHERE E1.i NOT IN (E2.i, E3.i)
AND E2.i NOT IN (E1.i, E3.i )
AND E3.i NOT IN (E1.i, E2.i)
and e2.i > e1.i and e3.i > e2.i
which did the trick. Thanks for your thoughts though.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:OrowGe2RFHA.2964@.TK2MSFTNGP15.phx.gbl...
> Create table a ( id int not null)
> declare @.a int
> select @. = 1
> While @.a <= 35
> begin
> insert into a values (@.a)
> select @.a = @.a + 1
> end
> select a.id, b.id, c.id from
> a inner join a as b on a.id = b.id
> inner join c on a.id = c.id
>
> hope this is what you are looking for
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "D" <Dave@.nothing.net> wrote in message
> news:u4$Snm0RFHA.1172@.TK2MSFTNGP12.phx.gbl...
>> so if I did combin(35,3) the result would be 6545 and the combinations
>> are
>> listed below. I know how to do it by creating three nested loops in c#
>> but
> I
>> think there's a way to do it in sql by using a cross join or self join
>> but
> I
>> don't see it.
>> Thanks
>>
>> 1 2 3
>> 1 2 4
>> 1 2 5
>> 1 2 6
>> 1 2 7
>> 1 2 8
>> 1 2 9
>> 1 2 10
>> 1 2 11
>> 1 2 12
>> 1 2 13
>> 1 2 14
>> 1 2 15
>> 1 2 16
>> 1 2 17
>> 1 2 18
>> 1 2 19
>> 1 2 20
>> 1 2 21
>> 1 2 22
>> 1 2 23
>> 1 2 24
>> 1 2 25
>> 1 2 26
>> 1 2 27
>> 1 2 28
>> 1 2 29
>> 1 2 30
>> 1 2 31
>> 1 2 32
>> 1 2 33
>> 1 2 34
>> 1 2 35
>> 1 3 4
>> 1 3 5
>> 1 3 6
>> 1 3 7
>> 1 3 8
>> 1 3 9
>> 1 3 10
>> 1 3 11
>> 1 3 12
>> 1 3 13
>> 1 3 14
>> 1 3 15
>> 1 3 16
>> 1 3 17
>> 1 3 18
>> 1 3 19
>> 1 3 20
>> 1 3 21
>> 1 3 22
>> 1 3 23
>> 1 3 24
>> 1 3 25
>> 1 3 26
>> 1 3 27
>> 1 3 28
>> 1 3 29
>> 1 3 30
>> 1 3 31
>> 1 3 32
>> 1 3 33
>> 1 3 34
>> 1 3 35
>> 1 4 5
>>
>sql

I would like to generate the combinations of the excel function combin in t-sql

so if I did combin(35,3) the result would be 6545 and the combinations are
listed below. I know how to do it by creating three nested loops in c# but I
think there's a way to do it in sql by using a cross join or self join but I
don't see it.
Thanks
1 2 3
1 2 4
1 2 5
1 2 6
1 2 7
1 2 8
1 2 9
1 2 10
1 2 11
1 2 12
1 2 13
1 2 14
1 2 15
1 2 16
1 2 17
1 2 18
1 2 19
1 2 20
1 2 21
1 2 22
1 2 23
1 2 24
1 2 25
1 2 26
1 2 27
1 2 28
1 2 29
1 2 30
1 2 31
1 2 32
1 2 33
1 2 34
1 2 35
1 3 4
1 3 5
1 3 6
1 3 7
1 3 8
1 3 9
1 3 10
1 3 11
1 3 12
1 3 13
1 3 14
1 3 15
1 3 16
1 3 17
1 3 18
1 3 19
1 3 20
1 3 21
1 3 22
1 3 23
1 3 24
1 3 25
1 3 26
1 3 27
1 3 28
1 3 29
1 3 30
1 3 31
1 3 32
1 3 33
1 3 34
1 3 35
1 4 5Create table a ( id int not null)
declare @.a int
select @. = 1
While @.a <= 35
begin
insert into a values (@.a)
select @.a = @.a + 1
end
select a.id, b.id, c.id from
a inner join a as b on a.id = b.id
inner join c on a.id = c.id
hope this is what you are looking for
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"D" <Dave@.nothing.net> wrote in message
news:u4$Snm0RFHA.1172@.TK2MSFTNGP12.phx.gbl...
> so if I did combin(35,3) the result would be 6545 and the combinations are
> listed below. I know how to do it by creating three nested loops in c# but
I
> think there's a way to do it in sql by using a cross join or self join but
I
> don't see it.
> Thanks
>
> 1 2 3
> 1 2 4
> 1 2 5
> 1 2 6
> 1 2 7
> 1 2 8
> 1 2 9
> 1 2 10
> 1 2 11
> 1 2 12
> 1 2 13
> 1 2 14
> 1 2 15
> 1 2 16
> 1 2 17
> 1 2 18
> 1 2 19
> 1 2 20
> 1 2 21
> 1 2 22
> 1 2 23
> 1 2 24
> 1 2 25
> 1 2 26
> 1 2 27
> 1 2 28
> 1 2 29
> 1 2 30
> 1 2 31
> 1 2 32
> 1 2 33
> 1 2 34
> 1 2 35
> 1 3 4
> 1 3 5
> 1 3 6
> 1 3 7
> 1 3 8
> 1 3 9
> 1 3 10
> 1 3 11
> 1 3 12
> 1 3 13
> 1 3 14
> 1 3 15
> 1 3 16
> 1 3 17
> 1 3 18
> 1 3 19
> 1 3 20
> 1 3 21
> 1 3 22
> 1 3 23
> 1 3 24
> 1 3 25
> 1 3 26
> 1 3 27
> 1 3 28
> 1 3 29
> 1 3 30
> 1 3 31
> 1 3 32
> 1 3 33
> 1 3 34
> 1 3 35
> 1 4 5
>|||Thanks. There were a couple I think just typo syntax errors but once correct
it produced this
111
222
333
444
...
I found another sample and was able to modify that to get the results I
wanted, it went like this
CREATE TABLE Elements (i INTEGER NOT NULL);
declare @.index int
set @.index = 1
while @.index <= 10
begin
INSERT INTO Elements VALUES (@.index);
set @.index = @.index + 1
end
SELECT E1.i A, E2.i B , E3.i C
FROM Elements AS E1, Elements AS E2, Elements AS E3
WHERE E1.i NOT IN (E2.i, E3.i)
AND E2.i NOT IN (E1.i, E3.i )
AND E3.i NOT IN (E1.i, E2.i)
and e2.i > e1.i and e3.i > e2.i
which did the trick. Thanks for your thoughts though.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:OrowGe2RFHA.2964@.TK2MSFTNGP15.phx.gbl...
> Create table a ( id int not null)
> declare @.a int
> select @. = 1
> While @.a <= 35
> begin
> insert into a values (@.a)
> select @.a = @.a + 1
> end
> select a.id, b.id, c.id from
> a inner join a as b on a.id = b.id
> inner join c on a.id = c.id
>
> hope this is what you are looking for
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "D" <Dave@.nothing.net> wrote in message
> news:u4$Snm0RFHA.1172@.TK2MSFTNGP12.phx.gbl...
> I
> I
>

I need help normalizing this table.

So I'm creating an administrative back end for a site that's already been created, and whoever made the tables the site uses didn't know much about database design. So I need to normalize this table of Links so it can be easier to have someone make changes and updates to it, but then I need to put all my normalized tables back together to create a View exactly like the old table which the old site can select from. Basically the stipulation is I can't change the code for the old site so I have to make it think it's still selecting from the same table with the same type of parameters. Is it worth doing all this? Or should I just tough it out with this really ugly table?
Here's the table:

and here's the site that uses this table:
http://waahp.byu.edu/links.asp
Thanks!
~Cattrah~
You'll have to explain what the data is. E.g. Can there be duplicates in any of that lot? So canwww.aacp.org be in there twice? Where do these OrderWith values come from? Are there any existing constraints on the table?
|||there are no constraits except that the ID column is the auto generated primary key. Any links are allowed to be in there multiple times, although it really doesn't make much sense to do so. The whole context of the table is to generate a list of website links, as seen in the link below the table image. Most links have a Section(which does not have a URL), which under each section are the weblinks corresponding to it. There are also two special types of links, email links, which can also appear under a Section or be on their own, and Links that appear at the top. IE they will just be listed at the top of the page reguardless if its under a section or not, if it does have a section, it will also appear there. descriptions will appear to the right of all links except email links in which case it will appear to the left. Sections should not have descriptions. OrderWith is a pretty worthless column the way it stands, it defines the order in which the items are selected from the database, in this case alphabetical. With the first links in which the OrderWith is numbered, changing the numbering would change the order that the links appear on the page. In the other cases if you wanted two links under a section to be ordered non alphabetically, you could order them with numbers or just change the order with values to cause them to order a different way.
That's really the whole thing, if you look at the old site, it makes more sense.
~Cattrah~|||Looks like a lot of SQL for one Page of links.
I would not normalize it: There is content and layout information in the DB and the result is a page that looks more like a static HTML file.
If you realy need to touch it I would try to convince my customer to use a simple Category-Link Relation or enter HTML with a Richtextbox.
But I guess this is not an option for you...
regards
Mathias

I need help creating a procedure

Please I need some body to guide me here. I dont know how to create a store procedure.

If the user wants to delete one transaction I need to do the following to be able to delete the transaction. What I'm trying to do here is update the items to the state that they were before the transaction.

What I want to do is something like this, I never have made a store procedure this is just an example to make easy to understand what I need to do.

CREATE PROCEDURE `videodb`.`CancelTransaction` (transaction_id INT)
BEGIN

DECLARE sale_types VARCHAR(25);
DECLARE sale_ids INT;
DECLARE item_ids INT;
DECLARE deposit_ids INT;

/*Here I need to select the required fields that are needed to do what’s next*/

SELECT rents.id, rents.item_id, rents.type, deposit_id INTO sale_ids, item_ids, sale_types, deposit_ids
FROM VideoDB.transactions
INNER JOIN VideoDB.rents ON rents.transaction_id=transaction.id
WHERE id=transaction_id;

WHILE (sale_types <> NULL) DO

SELECT CASE WHEN (sale_types='Rent') THEN

/*Here the rent is deleted and the item is updated to available*/

DELETE FROM VideoDB.rents WHERE id=sale_ids;

UPDATE VideoDB.TransactionItems SET status='Available' WHERE id=item_ids;

WHEN (sale_types='Sale') Then

/*Here the sale is deleted and if the item that was sold was a sale item the inventory is updated and if the item that was sold was a rent item the item is updated to available*/

DECLARE cur_keep_inventory BIT;
DECLARE cur_item_type VARCHAR(25);
DECLARE cur_item_inventory INT;

DELETE FROM VideoDB.rents WHERE id=sale_ids;

SELECT keep_inventory, inventory, item_type INTO cur_keep_inventory, cur_item_inventory, cur_item_type
FROM VideoDB.items WHERE id=item_ids;

SELECT CASE WHEN (cur_keep_inventory=1 And cur_item_type='Sale') THEN

UPDATE VideoDB.items SET quantity=cur_item_inventory+1 WHERE id=item_ids;

WHEN (cur_item_type='Rent') THEN

UPDATE VideoDB.items SET status='Avalable' WHERE id=item_ids;

END;

WHEN (sale_types='Overdue Payment') Then

/*Here the Overdue deposit is added to the current overdue and is deleted*/

DECLARE cur_paid_amount DOUBLE;
DECLARE cur_deposit_id INT;
DECLARE cur_overdue_amount DOUBLE;

SELECT paid_amount, deposit_id, r.paid_amount INTO cur_paid_amount, cur_deposit_id, cur_overdue_amount
FROM VideoDB.rents, VideoDB.rents r WHERE rents.deposit_id=r.id AND id=sale_ids;

UPDATE VideoDB.rents SET paid_amount=cur_overdue_amount+cur_paid_amount WHERE id=cur_deposit_id;

DELETE FROM VideoDB.rents WHERE id=sale_ids;

WHEN (sale_types='Debt Payment') Then

/*Here the Debt deposit is added to the current debt and is deleted*/

DECLARE cur_paid_amount DOUBLE;
DECLARE cur_deposit_id INT;
DECLARE cur_debt_amount DOUBLE;

SELECT paid_amount, deposit_id, r.paid_amount INTO cur_paid_amount, cur_deposit_id, cur_overdue_amount
FROM VideoDB.rents, VideoDB.rents r WHERE rents.deposit_id=r.id AND id=sale_ids;

UPDATE VideoDB.rents SET paid_amount=cur_debt_amount+cur_paid_amount WHERE id=cur_deposit_id;

DELETE FROM VideoDB.rents WHERE id=sale_ids;

END;
END WHILE;
END

Amendez:

Your pseudocode looks like you have the problem pretty well thought through. The next step for you is to convert your pseudocode into actual SQL syntax. Use books online and begin the conversion. Since you are new to this my feeling is that you need to do this learning phase on your own as much as possible so I will leave the bulk of this work to you. Some topics that you are going to need to examine are (1) variables, (2) cursors -- I don't like cursors, but that appears to be the way your pseudocode is aimed, (3) IF and ELSE statements, (4) WHILE, (5) CREATE PROCEDURE and more. Make a full pass at converting your pseudocode into a stored procedure and then let us know how it is going.

Something that you are going to need to learn somewhere down the road are reasons to avoid using cursors. The main thing that you need to know about this for the moment is that MS SQL Server is more efficient when operating on sets of records than when working on records one at a time. Think about this for now and we will discuss this after you have completed your first pass.

Good Luck,
Dave