problem with sql

I've been having problems with the following query. It falls over on the first BEGIN but I can't se why.

Any sugguestions?

Set transaction isolation level read uncommitted

Declare @.Date DateTime
Declare @.Msg VarChar(255)
set @.Date = Getdate()-44

If (Select z.lastamendedby from dbo.intray as z
Where z.lastamendedby in
(select x.userid from dbo.useractivity as x left join dbo.users as y on x.userid = y.user_id
where x.lastactivityTime < @.Date))
--and y.ntSuspended = 0)
--and z.lastamended > @.Date
Begin

set @.Msg = "These users have done work since the 44 day period, don't suspend"
Print @.Msg
End

Else

Begin
Select z.lastamendedby from dbo.intray as z
Where lastamendedby in
(select x.userid from dbo.useractivity as x left join dbo.users as y on x.userid = y.user_id
where x.lastactivityTime < @.Date
and y.ntSuspended = 0)
Set @.Msg = "These users haven't done any work since the 44 day period, suspend"
Print @.Msg

End

Cheers,

Jim

Because you didn't finish your IF.

If you reduce it down, maybe you'll see:

IF (SELECT lastamendedby)
BEGIN

Just out of curiosity, what company do you work for? Cause I'm definately interested in finding one that only suspends you if you haven't done anything for 44 days ;-p

Problem With SQL

Hi all. I have a huge complex problem:
Table: salesTran (List all the sames transactions. One transaction may
have two parties involved.)
AgentIDIncomePartnerIDPartner_IncomeDate
------
A000015000A00002500021/03/2005
A000025000A00003500022/04/2005
A000045000A00005500020/05/2005
A000035000A00002500031/03/2005
A000065000A00001500001/01/2005
A000075000A00021500001/01/2005
A000085000A00033500001/01/2005
(ETC)
Table: AgentsParticulars (Contains Details On Agents)
AgentIDNameDepartmentStatusManagerID
------
A00001ErnieResidentialActiveNULL
A00002BenResidentialActiveA00001
A00003KeithResidentialActiveA00001
A00004BillResidentialActiveA00002
A00005CrystalResidentialActiveA00002
A00006JeanResidentialActiveA00003
A00007JoshuaResidentialActiveA00031
(ETC)
What I wanted to do was to create a Stored Procedure so that when I
pass an AgentID into in, it'll return me the next level agent details
as follows:
ID Passed In: A00001
AgentIDNameDepartmentStatusTotalIncom TotalTeamIncome
------A00002BenResidentialActive15000
10000
A00003KeithResidentialActive10000 5000
The TotalIncome column will display all income of the agent (Total
Income + Total PartnerIncome). The TotalTeamIncome will display the sum
of the totalIncome of all agents linked (refer to the AgentsParticulars
table). So, in the case of A00002, the TotalTeamIncome will display the
total sum of the total income of A00002 and A00005 (and any agent that
has A00002 and A00005 as their manager).
Anyone has any workarounds on this? I'm using SQL 7.0.
Thanks so much.
Ernie
Look atv this script written by Itzik Ben-Gan
Perhars it is not exactly what you wanted but I'm sure it gives you an idea
to solve the problem
CREATE TABLE Employees
(
empid int NOT NULL,
mgrid int NULL,
empname varchar(25) NOT NULL,
salary money NOT NULL,
CONSTRAINT PK_Employees_empid PRIMARY KEY(empid),
CONSTRAINT FK_Employees_mgrid_empid
FOREIGN KEY(mgrid)
REFERENCES Employees(empid)
)
CREATE INDEX idx_nci_mgrid ON Employees(mgrid)
INSERT INTO Employees VALUES(1 , NULL, 'Nancy' , $10000.00)
INSERT INTO Employees VALUES(2 , 1 , 'Andrew' , $5000.00)
INSERT INTO Employees VALUES(3 , 1 , 'Janet' , $5000.00)
INSERT INTO Employees VALUES(4 , 1 , 'Margaret', $5000.00)
INSERT INTO Employees VALUES(5 , 2 , 'Steven' , $2500.00)
INSERT INTO Employees VALUES(6 , 2 , 'Michael' , $2500.00)
INSERT INTO Employees VALUES(7 , 3 , 'Robert' , $2500.00)
INSERT INTO Employees VALUES(8 , 3 , 'Laura' , $2500.00)
INSERT INTO Employees VALUES(9 , 3 , 'Ann' , $2500.00)
INSERT INTO Employees VALUES(10, 4 , 'Ina' , $2500.00)
INSERT INTO Employees VALUES(11, 7 , 'David' , $2000.00)
INSERT INTO Employees VALUES(12, 7 , 'Ron' , $2000.00)
INSERT INTO Employees VALUES(13, 7 , 'Dan' , $2000.00)
INSERT INTO Employees VALUES(14, 11 , 'James' , $1500.00)
GO
CREATE FUNCTION dbo.ufn_GetSubtree
(
@.mgrid AS int
)
RETURNS @.tree table
(
empid int NOT NULL,
mgrid int NULL,
empname varchar(25) NOT NULL,
salary money NOT NULL,
lvl int NOT NULL,
path varchar(900) NOT NULL
)
AS
BEGIN
DECLARE @.lvl AS int, @.path AS varchar(900)
SELECT @.lvl = 0, @.path = '.'
INSERT INTO @.tree
SELECT empid, mgrid, empname, salary,
@.lvl, '.' + CAST(empid AS varchar(10)) + '.'
FROM Employees
WHERE empid = @.mgrid
WHILE @.@.ROWCOUNT > 0
BEGIN
SET @.lvl = @.lvl + 1
INSERT INTO @.tree
SELECT E.empid, E.mgrid, E.empname, E.salary,
@.lvl, T.path + CAST(E.empid AS varchar(10)) + '.'
FROM Employees AS E JOIN @.tree AS T
ON E.mgrid = T.empid AND T.lvl = @.lvl - 1
END
RETURN
END
GO
SELECT empid, mgrid, empname, salary
FROM ufn_GetSubtree(3)
GO
/*
empid mgrid empname salary
2 1 Andrew 5000.0000
5 2 Steven 2500.0000
6 2 Michael 2500.0000
*/
/*
SELECT REPLICATE (' | ', lvl) + empname AS employee
FROM ufn_GetSubtree(1)
ORDER BY path
*/
/*
employee
Nancy
| Andrew
| | Steven
| | Michael
| Janet
| | Robert
| | | David
| | | | James
| | | Ron
| | | Dan
| | Laura
| | Ann
| Margaret
| | Ina
*/
"Ernie" <ernie.song@.orangetee.com> wrote in message
news:1134355686.843988.65790@.g43g2000cwa.googlegro ups.com...
> Hi all. I have a huge complex problem:
> Table: salesTran (List all the sames transactions. One transaction may
> have two parties involved.)
> AgentID Income PartnerID Partner_Income Date
> ------
> A00001 5000 A00002 5000 21/03/2005
> A00002 5000 A00003 5000 22/04/2005
> A00004 5000 A00005 5000 20/05/2005
> A00003 5000 A00002 5000 31/03/2005
> A00006 5000 A00001 5000 01/01/2005
> A00007 5000 A00021 5000 01/01/2005
> A00008 5000 A00033 5000 01/01/2005
> (ETC)
> Table: AgentsParticulars (Contains Details On Agents)
> AgentID Name Department Status ManagerID
> ------
> A00001 Ernie Residential Active NULL
> A00002 Ben Residential Active A00001
> A00003 Keith Residential Active A00001
> A00004 Bill Residential Active A00002
> A00005 Crystal Residential Active A00002
> A00006 Jean Residential Active A00003
> A00007 Joshua Residential Active A00031
> (ETC)
> What I wanted to do was to create a Stored Procedure so that when I
> pass an AgentID into in, it'll return me the next level agent details
> as follows:
> ID Passed In: A00001
> AgentID Name Department Status TotalIncom TotalTeamIncome
> ------A00002
> Ben Residential Active 15000
> 10000
> A00003 Keith Residential Active 10000 5000
>
> The TotalIncome column will display all income of the agent (Total
> Income + Total PartnerIncome). The TotalTeamIncome will display the sum
> of the totalIncome of all agents linked (refer to the AgentsParticulars
> table). So, in the case of A00002, the TotalTeamIncome will display the
> total sum of the total income of A00002 and A00005 (and any agent that
> has A00002 and A00005 as their manager).
> Anyone has any workarounds on this? I'm using SQL 7.0.
> Thanks so much.
>
|||Thanks Uri. I did a Stored Procedure with something to this extent.
However, as the database stores a few thousand records for each person,
the time taken to retrieve the records online is extremely slow. So, was
just wondering if there is a workaround it. The main prob I face here is
that the initial design of the database wasn't good. So, with that prob,
it has created a mountain of other problems. Yups. Oh, by the way, I'm
using SQL 7, so, FUNCTION don't really work for me. Thanks for your
reply though. Really appreciate it.
*** Sent via Developersdex http://www.codecomments.com ***
|||Sorry, did not read properly that you are using SQL Server 7.0
However , you can re-write the UDF as a Stored Procedure as you did probably
and having properly defined indexeses you'll not have any problems in terms
of performance ( a few thousand records for each person is really small
amount of data)
"Ernie Song" <ernie.song@.orangetee.com> wrote in message
news:OzoOFau$FHA.1408@.TK2MSFTNGP15.phx.gbl...
> Thanks Uri. I did a Stored Procedure with something to this extent.
> However, as the database stores a few thousand records for each person,
> the time taken to retrieve the records online is extremely slow. So, was
> just wondering if there is a workaround it. The main prob I face here is
> that the initial design of the database wasn't good. So, with that prob,
> it has created a mountain of other problems. Yups. Oh, by the way, I'm
> using SQL 7, so, FUNCTION don't really work for me. Thanks for your
> reply though. Really appreciate it.
>
> *** Sent via Developersdex http://www.codecomments.com ***
|||Well I couldnt help but notice one thing that could be a serious problem for
this and other things you would want to do with this table: it is badly
denormalized. The partner income column is 100% redundant and should be
completely removed...
It doesnt seem right what you have going with the partnerID column either in
terms of normalization. It would make your procedure easier and would help
normalize your DB if you split this into 2 tables.

Problem With SQL

Hi all. I have a huge complex problem:
Table: salesTran (List all the sames transactions. One transaction may
have two parties involved.)
AgentID Income PartnerID Partner_Income
Date
----
---
A00001 5000 A00002 5000 21/03/2005
A00002 5000 A00003 5000 22/04/2005
A00004 5000 A00005 5000 20/05/2005
A00003 5000 A00002 5000 31/03/2005
A00006 5000 A00001 5000 01/01/2005
A00007 5000 A00021 5000 01/01/2005
A00008 5000 A00033 5000 01/01/2005
(ETC)
Table: AgentsParticulars (Contains Details On Agents)
AgentID Name Department Status ManagerI
D
----
---
A00001 Ernie Residential Active NULL
A00002 Ben Residential Active A00001
A00003 Keith Residential Active A00001
A00004 Bill Residential Active A00002
A00005 Crystal Residential Active A0000
2
A00006 Jean Residential Active A00003
A00007 Joshua Residential Active A00031
(ETC)
What I wanted to do was to create a Stored Procedure so that when I
pass an AgentID into in, it'll return me the next level agent details
as follows:
ID Passed In: A00001
AgentID Name Department Status TotalInco
m TotalTeamIncome
----
----A00002 Ben Residential
Active 15000
10000
A00003 Keith Residential Active 10000 5000
The TotalIncome column will display all income of the agent (Total
Income + Total PartnerIncome). The TotalTeamIncome will display the sum
of the totalIncome of all agents linked (refer to the AgentsParticulars
table). So, in the case of A00002, the TotalTeamIncome will display the
total sum of the total income of A00002 and A00005 (and any agent that
has A00002 and A00005 as their manager).
Anyone has any workarounds on this? I'm using SQL 7.0.
Thanks so much.Ernie
Look atv this script written by Itzik Ben-Gan
Perhars it is not exactly what you wanted but I'm sure it gives you an idea
to solve the problem
CREATE TABLE Employees
(
empid int NOT NULL,
mgrid int NULL,
empname varchar(25) NOT NULL,
salary money NOT NULL,
CONSTRAINT PK_Employees_empid PRIMARY KEY(empid),
CONSTRAINT FK_Employees_mgrid_empid
FOREIGN KEY(mgrid)
REFERENCES Employees(empid)
)
CREATE INDEX idx_nci_mgrid ON Employees(mgrid)
INSERT INTO Employees VALUES(1 , NULL, 'Nancy' , $10000.00)
INSERT INTO Employees VALUES(2 , 1 , 'Andrew' , $5000.00)
INSERT INTO Employees VALUES(3 , 1 , 'Janet' , $5000.00)
INSERT INTO Employees VALUES(4 , 1 , 'Margaret', $5000.00)
INSERT INTO Employees VALUES(5 , 2 , 'Steven' , $2500.00)
INSERT INTO Employees VALUES(6 , 2 , 'Michael' , $2500.00)
INSERT INTO Employees VALUES(7 , 3 , 'Robert' , $2500.00)
INSERT INTO Employees VALUES(8 , 3 , 'Laura' , $2500.00)
INSERT INTO Employees VALUES(9 , 3 , 'Ann' , $2500.00)
INSERT INTO Employees VALUES(10, 4 , 'Ina' , $2500.00)
INSERT INTO Employees VALUES(11, 7 , 'David' , $2000.00)
INSERT INTO Employees VALUES(12, 7 , 'Ron' , $2000.00)
INSERT INTO Employees VALUES(13, 7 , 'Dan' , $2000.00)
INSERT INTO Employees VALUES(14, 11 , 'James' , $1500.00)
GO
CREATE FUNCTION dbo.ufn_GetSubtree
(
@.mgrid AS int
)
RETURNS @.tree table
(
empid int NOT NULL,
mgrid int NULL,
empname varchar(25) NOT NULL,
salary money NOT NULL,
lvl int NOT NULL,
path varchar(900) NOT NULL
)
AS
BEGIN
DECLARE @.lvl AS int, @.path AS varchar(900)
SELECT @.lvl = 0, @.path = '.'
INSERT INTO @.tree
SELECT empid, mgrid, empname, salary,
@.lvl, '.' + CAST(empid AS varchar(10)) + '.'
FROM Employees
WHERE empid = @.mgrid
WHILE @.@.ROWCOUNT > 0
BEGIN
SET @.lvl = @.lvl + 1
INSERT INTO @.tree
SELECT E.empid, E.mgrid, E.empname, E.salary,
@.lvl, T.path + CAST(E.empid AS varchar(10)) + '.'
FROM Employees AS E JOIN @.tree AS T
ON E.mgrid = T.empid AND T.lvl = @.lvl - 1
END
RETURN
END
GO
SELECT empid, mgrid, empname, salary
FROM ufn_GetSubtree(3)
GO
/*
empid mgrid empname salary
2 1 Andrew 5000.0000
5 2 Steven 2500.0000
6 2 Michael 2500.0000
*/
/*
SELECT REPLICATE (' | ', lvl) + empname AS employee
FROM ufn_GetSubtree(1)
ORDER BY path
*/
/*
employee
--
Nancy
| Andrew
| | Steven
| | Michael
| Janet
| | Robert
| | | David
| | | | James
| | | Ron
| | | Dan
| | Laura
| | Ann
| Margaret
| | Ina
*/
"Ernie" <ernie.song@.orangetee.com> wrote in message
news:1134355686.843988.65790@.g43g2000cwa.googlegroups.com...
> Hi all. I have a huge complex problem:
> Table: salesTran (List all the sames transactions. One transaction may
> have two parties involved.)
> AgentID Income PartnerID Partner_Income Date
> ----
---
> A00001 5000 A00002 5000 21/03/2005
> A00002 5000 A00003 5000 22/04/2005
> A00004 5000 A00005 5000 20/05/2005
> A00003 5000 A00002 5000 31/03/2005
> A00006 5000 A00001 5000 01/01/2005
> A00007 5000 A00021 5000 01/01/2005
> A00008 5000 A00033 5000 01/01/2005
> (ETC)
> Table: AgentsParticulars (Contains Details On Agents)
> AgentID Name Department Status ManagerID
> ----
---
> A00001 Ernie Residential Active NULL
> A00002 Ben Residential Active A00001
> A00003 Keith Residential Active A00001
> A00004 Bill Residential Active A00002
> A00005 Crystal Residential Active A00002
> A00006 Jean Residential Active A00003
> A00007 Joshua Residential Active A00031
> (ETC)
> What I wanted to do was to create a Stored Procedure so that when I
> pass an AgentID into in, it'll return me the next level agent details
> as follows:
> ID Passed In: A00001
> AgentID Name Department Status TotalIncom TotalTeamIncome
> ----
----A00002
> Ben Residential Active 15000
> 10000
> A00003 Keith Residential Active 10000 5000
>
> The TotalIncome column will display all income of the agent (Total
> Income + Total PartnerIncome). The TotalTeamIncome will display the sum
> of the totalIncome of all agents linked (refer to the AgentsParticulars
> table). So, in the case of A00002, the TotalTeamIncome will display the
> total sum of the total income of A00002 and A00005 (and any agent that
> has A00002 and A00005 as their manager).
> Anyone has any workarounds on this? I'm using SQL 7.0.
> Thanks so much.
>|||Thanks Uri. I did a Stored Procedure with something to this extent.
However, as the database stores a few thousand records for each person,
the time taken to retrieve the records online is extremely slow. So, was
just wondering if there is a workaround it. The main prob I face here is
that the initial design of the database wasn't good. So, with that prob,
it has created a mountain of other problems. Yups. Oh, by the way, I'm
using SQL 7, so, FUNCTION don't really work for me. Thanks for your
reply though. Really appreciate it.
*** Sent via Developersdex http://www.codecomments.com ***|||Sorry, did not read properly that you are using SQL Server 7.0
However , you can re-write the UDF as a Stored Procedure as you did probably
and having properly defined indexeses you'll not have any problems in terms
of performance ( a few thousand records for each person is really small
amount of data)
"Ernie Song" <ernie.song@.orangetee.com> wrote in message
news:OzoOFau$FHA.1408@.TK2MSFTNGP15.phx.gbl...
> Thanks Uri. I did a Stored Procedure with something to this extent.
> However, as the database stores a few thousand records for each person,
> the time taken to retrieve the records online is extremely slow. So, was
> just wondering if there is a workaround it. The main prob I face here is
> that the initial design of the database wasn't good. So, with that prob,
> it has created a mountain of other problems. Yups. Oh, by the way, I'm
> using SQL 7, so, FUNCTION don't really work for me. Thanks for your
> reply though. Really appreciate it.
>
> *** Sent via Developersdex http://www.codecomments.com ***|||Well I couldnt help but notice one thing that could be a serious problem for
this and other things you would want to do with this table: it is badly
denormalized. The partner income column is 100% redundant and should be
completely removed...
It doesnt seem right what you have going with the partnerID column either in
terms of normalization. It would make your procedure easier and would help
normalize your DB if you split this into 2 tables.sql

Problem With SQL

Hi all. I have a huge complex problem:
Table: salesTran (List all the sames transactions. One transaction may
have two parties involved.)
AgentID Income PartnerID Partner_Income Date
------
A00001 5000 A00002 5000 21/03/2005
A00002 5000 A00003 5000 22/04/2005
A00004 5000 A00005 5000 20/05/2005
A00003 5000 A00002 5000 31/03/2005
A00006 5000 A00001 5000 01/01/2005
A00007 5000 A00021 5000 01/01/2005
A00008 5000 A00033 5000 01/01/2005
(ETC)
Table: AgentsParticulars (Contains Details On Agents)
AgentID Name Department Status ManagerID
------
A00001 Ernie Residential Active NULL
A00002 Ben Residential Active A00001
A00003 Keith Residential Active A00001
A00004 Bill Residential Active A00002
A00005 Crystal Residential Active A00002
A00006 Jean Residential Active A00003
A00007 Joshua Residential Active A00031
(ETC)
What I wanted to do was to create a Stored Procedure so that when I
pass an AgentID into in, it'll return me the next level agent details
as follows:
ID Passed In: A00001
AgentID Name Department Status TotalIncom TotalTeamIncome
------A00002 Ben Residential Active 15000
10000
A00003 Keith Residential Active 10000 5000
The TotalIncome column will display all income of the agent (Total
Income + Total PartnerIncome). The TotalTeamIncome will display the sum
of the totalIncome of all agents linked (refer to the AgentsParticulars
table). So, in the case of A00002, the TotalTeamIncome will display the
total sum of the total income of A00002 and A00005 (and any agent that
has A00002 and A00005 as their manager).
Anyone has any workarounds on this? I'm using SQL 7.0.
Thanks so much.Ernie
Look atv this script written by Itzik Ben-Gan
Perhars it is not exactly what you wanted but I'm sure it gives you an idea
to solve the problem
CREATE TABLE Employees
(
empid int NOT NULL,
mgrid int NULL,
empname varchar(25) NOT NULL,
salary money NOT NULL,
CONSTRAINT PK_Employees_empid PRIMARY KEY(empid),
CONSTRAINT FK_Employees_mgrid_empid
FOREIGN KEY(mgrid)
REFERENCES Employees(empid)
)
CREATE INDEX idx_nci_mgrid ON Employees(mgrid)
INSERT INTO Employees VALUES(1 , NULL, 'Nancy' , $10000.00)
INSERT INTO Employees VALUES(2 , 1 , 'Andrew' , $5000.00)
INSERT INTO Employees VALUES(3 , 1 , 'Janet' , $5000.00)
INSERT INTO Employees VALUES(4 , 1 , 'Margaret', $5000.00)
INSERT INTO Employees VALUES(5 , 2 , 'Steven' , $2500.00)
INSERT INTO Employees VALUES(6 , 2 , 'Michael' , $2500.00)
INSERT INTO Employees VALUES(7 , 3 , 'Robert' , $2500.00)
INSERT INTO Employees VALUES(8 , 3 , 'Laura' , $2500.00)
INSERT INTO Employees VALUES(9 , 3 , 'Ann' , $2500.00)
INSERT INTO Employees VALUES(10, 4 , 'Ina' , $2500.00)
INSERT INTO Employees VALUES(11, 7 , 'David' , $2000.00)
INSERT INTO Employees VALUES(12, 7 , 'Ron' , $2000.00)
INSERT INTO Employees VALUES(13, 7 , 'Dan' , $2000.00)
INSERT INTO Employees VALUES(14, 11 , 'James' , $1500.00)
GO
CREATE FUNCTION dbo.ufn_GetSubtree
(
@.mgrid AS int
)
RETURNS @.tree table
(
empid int NOT NULL,
mgrid int NULL,
empname varchar(25) NOT NULL,
salary money NOT NULL,
lvl int NOT NULL,
path varchar(900) NOT NULL
)
AS
BEGIN
DECLARE @.lvl AS int, @.path AS varchar(900)
SELECT @.lvl = 0, @.path = '.'
INSERT INTO @.tree
SELECT empid, mgrid, empname, salary,
@.lvl, '.' + CAST(empid AS varchar(10)) + '.'
FROM Employees
WHERE empid = @.mgrid
WHILE @.@.ROWCOUNT > 0
BEGIN
SET @.lvl = @.lvl + 1
INSERT INTO @.tree
SELECT E.empid, E.mgrid, E.empname, E.salary,
@.lvl, T.path + CAST(E.empid AS varchar(10)) + '.'
FROM Employees AS E JOIN @.tree AS T
ON E.mgrid = T.empid AND T.lvl = @.lvl - 1
END
RETURN
END
GO
SELECT empid, mgrid, empname, salary
FROM ufn_GetSubtree(3)
GO
/*
empid mgrid empname salary
2 1 Andrew 5000.0000
5 2 Steven 2500.0000
6 2 Michael 2500.0000
*/
/*
SELECT REPLICATE (' | ', lvl) + empname AS employee
FROM ufn_GetSubtree(1)
ORDER BY path
*/
/*
employee
--
Nancy
| Andrew
| | Steven
| | Michael
| Janet
| | Robert
| | | David
| | | | James
| | | Ron
| | | Dan
| | Laura
| | Ann
| Margaret
| | Ina
*/
"Ernie" <ernie.song@.orangetee.com> wrote in message
news:1134355686.843988.65790@.g43g2000cwa.googlegroups.com...
> Hi all. I have a huge complex problem:
> Table: salesTran (List all the sames transactions. One transaction may
> have two parties involved.)
> AgentID Income PartnerID Partner_Income Date
> ------
> A00001 5000 A00002 5000 21/03/2005
> A00002 5000 A00003 5000 22/04/2005
> A00004 5000 A00005 5000 20/05/2005
> A00003 5000 A00002 5000 31/03/2005
> A00006 5000 A00001 5000 01/01/2005
> A00007 5000 A00021 5000 01/01/2005
> A00008 5000 A00033 5000 01/01/2005
> (ETC)
> Table: AgentsParticulars (Contains Details On Agents)
> AgentID Name Department Status ManagerID
> ------
> A00001 Ernie Residential Active NULL
> A00002 Ben Residential Active A00001
> A00003 Keith Residential Active A00001
> A00004 Bill Residential Active A00002
> A00005 Crystal Residential Active A00002
> A00006 Jean Residential Active A00003
> A00007 Joshua Residential Active A00031
> (ETC)
> What I wanted to do was to create a Stored Procedure so that when I
> pass an AgentID into in, it'll return me the next level agent details
> as follows:
> ID Passed In: A00001
> AgentID Name Department Status TotalIncom TotalTeamIncome
> ------A00002
> Ben Residential Active 15000
> 10000
> A00003 Keith Residential Active 10000 5000
>
> The TotalIncome column will display all income of the agent (Total
> Income + Total PartnerIncome). The TotalTeamIncome will display the sum
> of the totalIncome of all agents linked (refer to the AgentsParticulars
> table). So, in the case of A00002, the TotalTeamIncome will display the
> total sum of the total income of A00002 and A00005 (and any agent that
> has A00002 and A00005 as their manager).
> Anyone has any workarounds on this? I'm using SQL 7.0.
> Thanks so much.
>|||Well I couldnt help but notice one thing that could be a serious problem for
this and other things you would want to do with this table: it is badly
denormalized. The partner income column is 100% redundant and should be
completely removed...
It doesnt seem right what you have going with the partnerID column either in
terms of normalization. It would make your procedure easier and would help
normalize your DB if you split this into 2 tables.

Problem with SET TRANSACTION ISOLATION LEVEL

I found that SET TRANSACTION ISOLATION LEVEL only sets the isolation level for the current CONNECTION.

1. What if I want to change the isolation level of the whole database? Which command is it ?

2. What if I want to use IsolationLevel of Serializable only with a transaction ( a part within a stored procedure)?

for ex. this is from a stored procedure

/* Blah Blah Blah Blah the T-SQL before the transaction */

BEGIN TRANSACTION myTran ; /*I want IsolationLevel = Serializable being used here*/

/* Do something Blah Blah Blah in this transaction */

COMMIT TRANSACTION myTran ; /* And here go back to default Isolation level */

/* Blah Blah Blah Blah the T-SQL after the transaction */

Thanks every body!!!

Pi

Hi Pi.

Answers:

1. No way to change the default isolation level for an entire database (more accurately, all connections to a database). With Sql 2005 you have the option of specifying that the usual default isolation level (read committed) use row-versioning instead of a lock-based isolation by enabling the READ_COMMITTED_SNAPSHOT database option, but that is all you can do there.

2. To achieve what you are trying to do, simply run the appropriate SET TRANSACTION ISOLATION LEVEL statements before and after you begin/end you transaction, as follows:

/* Blah Blah Blah Blah the T-SQL before the transaction */

-- ADD THIS HERE

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRANSACTION myTran ; /*I want IsolationLevel = Serializable being used here*/

/* Do something Blah Blah Blah in this transaction */

COMMIT TRANSACTION myTran ; /* And here go back to default Isolation level */

-- AND THIS HERE

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

/* Blah Blah Blah Blah the T-SQL after the transaction */

Hope that helps,

|||

Is there a way to set the TRANSACTION ISOLATION LEVEL SNAPSHOT for view. So all SQL for the View would run under SNAPSHOT isolation mode.

|||

In SQL Server, the transaction isolation is not tied to a specific transaction, it is a session property. So if you want do a query under a given isolation level, then simply set the session transaction isolation level to that one before you run your query. In your case, if you want the view to be accessed using SNAPSHOT isolation level, then simply set it before accessing the view, and change the isolation back to your default isolation level after the access to the viwe if you want.

There is no way currently in SQL Server to tie up a isolation level to a given object, like table, queue or a database either.

Thanks!

Problem with SET TRANSACTION ISOLATION LEVEL

I found that SET TRANSACTION ISOLATION LEVEL only sets the isolation level for the current CONNECTION.

1. What if I want to change the isolation level of the whole database? Which command is it ?

2. What if I want to use IsolationLevel of Serializable only with a transaction ( a part within a stored procedure)?

for ex. this is from a stored procedure

/* Blah Blah Blah Blah the T-SQL before the transaction */

BEGIN TRANSACTION myTran ; /*I want IsolationLevel = Serializable being used here*/

/* Do something Blah Blah Blah in this transaction */

COMMIT TRANSACTION myTran ; /* And here go back to default Isolation level */

/* Blah Blah Blah Blah the T-SQL after the transaction */

Thanks every body!!!

Pi

Hi Pi.

Answers:

1. No way to change the default isolation level for an entire database (more accurately, all connections to a database). With Sql 2005 you have the option of specifying that the usual default isolation level (read committed) use row-versioning instead of a lock-based isolation by enabling the READ_COMMITTED_SNAPSHOT database option, but that is all you can do there.

2. To achieve what you are trying to do, simply run the appropriate SET TRANSACTION ISOLATION LEVEL statements before and after you begin/end you transaction, as follows:

/* Blah Blah Blah Blah the T-SQL before the transaction */

-- ADD THIS HERE

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRANSACTION myTran ; /*I want IsolationLevel = Serializable being used here*/

/* Do something Blah Blah Blah in this transaction */

COMMIT TRANSACTION myTran ; /* And here go back to default Isolation level */

-- AND THIS HERE

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

/* Blah Blah Blah Blah the T-SQL after the transaction */

Hope that helps,

|||

Is there a way to set the TRANSACTION ISOLATION LEVEL SNAPSHOT for view. So all SQL for the View would run under SNAPSHOT isolation mode.

|||

In SQL Server, the transaction isolation is not tied to a specific transaction, it is a session property. So if you want do a query under a given isolation level, then simply set the session transaction isolation level to that one before you run your query. In your case, if you want the view to be accessed using SNAPSHOT isolation level, then simply set it before accessing the view, and change the isolation back to your default isolation level after the access to the viwe if you want.

There is no way currently in SQL Server to tie up a isolation level to a given object, like table, queue or a database either.

Thanks!

sql

Problem with SET TRANSACTION ISOLATION LEVEL

I found that SET TRANSACTION ISOLATION LEVEL only sets the isolation level for the current CONNECTION.

1. What if I want to change the isolation level of the whole database? Which command is it ?

2. What if I want to use IsolationLevel of Serializable only with a transaction ( a part within a stored procedure)?

for ex. this is from a stored procedure

/* Blah Blah Blah Blah the T-SQL before the transaction */

BEGIN TRANSACTION myTran ; /*I want IsolationLevel = Serializable being used here*/

/* Do something Blah Blah Blah in this transaction */

COMMIT TRANSACTION myTran ; /* And here go back to default Isolation level */

/* Blah Blah Blah Blah the T-SQL after the transaction */

Thanks every body!!!

Pi

Hi Pi.

Answers:

1. No way to change the default isolation level for an entire database (more accurately, all connections to a database). With Sql 2005 you have the option of specifying that the usual default isolation level (read committed) use row-versioning instead of a lock-based isolation by enabling the READ_COMMITTED_SNAPSHOT database option, but that is all you can do there.

2. To achieve what you are trying to do, simply run the appropriate SET TRANSACTION ISOLATION LEVEL statements before and after you begin/end you transaction, as follows:

/* Blah Blah Blah Blah the T-SQL before the transaction */

-- ADD THIS HERE

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRANSACTION myTran ; /*I want IsolationLevel = Serializable being used here*/

/* Do something Blah Blah Blah in this transaction */

COMMIT TRANSACTION myTran ; /* And here go back to default Isolation level */

-- AND THIS HERE

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

/* Blah Blah Blah Blah the T-SQL after the transaction */

Hope that helps,

|||

Is there a way to set the TRANSACTION ISOLATION LEVEL SNAPSHOT for view. So all SQL for the View would run under SNAPSHOT isolation mode.

|||

In SQL Server, the transaction isolation is not tied to a specific transaction, it is a session property. So if you want do a query under a given isolation level, then simply set the session transaction isolation level to that one before you run your query. In your case, if you want the view to be accessed using SNAPSHOT isolation level, then simply set it before accessing the view, and change the isolation back to your default isolation level after the access to the viwe if you want.

There is no way currently in SQL Server to tie up a isolation level to a given object, like table, queue or a database either.

Thanks!

Problem with rollback statement

Hi,

I have written a store procedure which inserts data into two tables. What I want do is to rollback transaction if the second insert fails. Below is a code.

Does anyone see my error?

Thanks,

poc1010

Create proc AddProducts

@.dcint=null,
@.pcint=null,
@.imagepathvarchar(50)=null,
@.typevarchar(2)=null,
@.descriptionvarchar(1000)=null,
@.gendervarchar(8)=null,
@.productidint=null,
@.pccodevarchar(2)=null,
@.weightvarchar(80)=null,
@.pricemoney=null,
@.activevarchar(1)=null

as

declare @.errorsave int
set @.errorsave=0
declare @.dg int

Begin transaction

insert productdescription(
designercategory,
productcategory,
imagepath,
type,
[description],
gender)
values(@.dc,
@.pc,
@.imagepath,
@.type,
@.description,
@.gender)

if @.@.error <> 0
set @.errorsave=@.@.error

set @.dg = @.@.identity

begin
insert Products(
productid,
designergroup,
designercategory,
productcategory,
pccode,
weight,
price,
active)
values(@.productid,
@.dg,
@.dc,
@.pc,
@.pccode,
@.weight,
@.price,
@.active)

if @.@.error <> 0
set @.errorsave=@.@.error
end

if @.errorsave <> 0
begin
print 'Insert into Products tables failed'
rollback transaction
return -5--Insert into Products tables failed
end

commit transaction
print 'Success'
return 0 --SuccessYou have begins and ends in useless spots. Whats the acual error message?|||My version of your stored proc (minor changes)

create proc AddProducts
 @.dc int = null,
 @.pc int = null,
 @.imagepath varchar(50) = null,
 @.type varchar(2) = null,
 @.description varchar(1000) = null,
 @.gender varchar(8) = null,
 @.productid int = null,
 @.pccode varchar(2) = null,
 @.weight varchar(80) = null,
 @.price money = null,
 @.active varchar(1) = null
as
begin
 declare @.dg int
 begin transaction
 insert into productdescription
 (designercategory,
 productcategory,
 imagepath,
 type,
 [description],
 gender)
 values(@.dc,
 @.pc,
 @.imagepath,
 @.type,
 @.description,
 @.gender)
 if @.@.error <> 0 or @.@.rowcount <> 1
 begin
 print 'Insert into Products tables failed'
 rollback transaction
 return -5 --Insert into Products tables failed
 end
 set @.dg = @.@.identity
 insert into Products
 (productid,
 designergroup,
 designercategory,
 productcategory,
 pccode,
 weight,
 price,
 active)
 values(@.productid,
 @.dg,
 @.dc,
 @.pc,
 @.pccode,
 @.weight,
 @.price,
 @.active)
 if @.@.error <> 0
 begin
 print 'Insert into Products tables failed'
 rollback transaction
 return -5 --Insert into Products tables failed
 end
 commit transaction
 print 'Success'
 return 0 --Success
end

|||My version of your stored proc (minor changes)

create proc AddProducts
 @.dc int = null,
 @.pc int = null,
 @.imagepath varchar(50) = null,
 @.type varchar(2) = null,
 @.description varchar(1000) = null,
 @.gender varchar(8) = null,
 @.productid int = null,
 @.pccode varchar(2) = null,
 @.weight varchar(80) = null,
 @.price money = null,
 @.active varchar(1) = null
as
begin
 declare @.dg int
 begin transaction
 insert into productdescription
 (designercategory,
 productcategory,
 imagepath,
 type,
 [description],
 gender)
 values(@.dc,
 @.pc,
 @.imagepath,
 @.type,
 @.description,
 @.gender)
 if @.@.error <> 0 or @.@.rowcount <> 1
 begin
 print 'Insert into Products tables failed'
 rollback transaction
 return -5 --Insert into Products tables failed
 end
 set @.dg = @.@.identity
 insert into Products
 (productid,
 designergroup,
 designercategory,
 productcategory,
 pccode,
 weight,
 price,
 active)
 values(@.productid,
 @.dg,
 @.dc,
 @.pc,
 @.pccode,
 @.weight,
 @.price,
 @.active)
 if @.@.error <> 0
 begin
 print 'Insert into Products tables failed'
 rollback transaction
 return -5 --Insert into Products tables failed
 end
 commit transaction
 print 'Success'
 return 0 --Success
end

|||None of you guys used ELSE. Your BEGIN/END's are a little whacked out. Honestly, I'm against returning in mid procedure if it's not necessary. You can easily follow through the entire procedure using an ELSE, then returning a specified value.|||Pierre,

Thanks for your example. I saw what I was doing wrong. Works great.

Thank you for your help.

poc1010|||That's just personal taste Lee. No real argument either way. Not in this case.|||You're right. That's why I said that I prefer the other way. Didn't say you were wrong, because it works fine.