Question about Stored Procedure - Split, Insert Questions 1

[tayorolls]

This question about the stored procedure is 2 part:

Following is the DDL for the test data which I am working on.

CREATE TABLE Test (
LawsonID varchar(20) primary key,
FirstName varchar(100),
LastName varchar(100),
Supervisor varchar(100)
)

INSERT INTO Test(LawsonID, FirstName, LastName, Supervisor) Values ('10956',
'Ali','Chowdhury','Pidaparti, Purna')
INSERT INTO Test(LawsonID, FirstName, LastName, Supervisor) Values ('14533',
'Bradley','Rosintoski','Pidaparti, Purna')
INSERT INTO Test(LawsonID, FirstName, LastName, Supervisor) Values ('18778',
'Brian','Emmett','Pidaparti, Purna')
INSERT INTO Test(LawsonID, FirstName, LastName, Supervisor) Values ('21337',
'Sathiya','Rajagopal','Pidaparti, Purna')
INSERT INTO Test(LawsonID, FirstName, LastName, Supervisor) Values ('T2276',
'Aravind','Seshadri','Rajagopal, Sathiya')
INSERT INTO Test(LawsonID, FirstName, LastName, Supervisor) Values ('T2756',
'Polar','Huang','Rajagopal, Sathiya')
INSERT INTO Test(LawsonID, FirstName, LastName, Supervisor) Values ('T951',
'Rishi','Kabra','Rajagopal, Sathiya')
INSERT INTO Test(LawsonID, FirstName, LastName, Supervisor) Values ('T2396',
'Johnson','Abraham','Chowdhury, Ali')
INSERT INTO Test(LawsonID, FirstName, LastName, Supervisor) Values ('T716',
'Kevin','Schmidt','Chowdhury, Ali')
INSERT INTO Test(LawsonID, FirstName, LastName, Supervisor) Values ('24990',
'Purna','Pidaparti','Ileana Rivera')

My goal is to build another table with the following columns:

LawsonID, FirstName, LastName, Supervisor, SupervisorID, SupervisorFirstName,
SupervisorLastName, NextLevelManagerID, NextLevelManagerFirstName,
NextLevelManagerLastName

A supervisor is also an employee, his LawsonID is found within the same table too.

My stored procedure till now is:

ALTER  Procedure BuildOrganization1

AS 

Declare @minLawson varchar
Set @minLawson = (Select min(LawsonID) from Test)
Print '@minLawsonID: ' + @minLawson

--Drop the table and re-build the table from scratch.
If exists(Select * From Information_Schema.Tables Where Table_Name = 'BuildOrganization' AND Table_Type = 'Base Table')
BEGIN
Drop Table [dbo].[BuildOrganization]
END

Create Table BuildOrganization(
LawsonID varchar(100) Primary Key,
FirstName varchar(100),
LastName varchar(100),
DirectManagerLawsonID varchar(100),
DirectManagerFirstName varchar(100),
DirectManagerLastName varchar(100),
NextLevelManagerLawsonID varchar(100),
NextLevelManagerFirstName varchar(100),
NextLevelManagerLastName varchar(100)
)

INSERT INTO BuildOrganization(LawsonID,FirstName,LastName,DirectManagerLawsonID,
DirectManagerFirstName,DirectManagerLastName,NextLevelManagerLawsonID,
NextLevelManagerFirstName,NextLevelManagerLastName)
SELECT LawsonID, FirstName, Lastname, NULL, NULL, NULL, NULL, NULL, NULL 
FROM Test
Where LawsonID = @minLawson
--Where LawsonID = '10956'

Execute BuildOrganization1

Now are my questions:

1. What I intend to do is: take data from the 'Test' table one row at a time. I chose to look at the minimum 'LawsonID' and then insert into BuildOrganization table. When I run "Select Min(LawsonID) from Test". It works. It returns 10956. But when I put it in the where clause, it does not return any rows. Why would that be?

2. Taking each row, I want to split the 'Supervisor' column into 'NextManagerFirstName' and 'NextManagerLastName'. Can I split it row by row and then insert into the 'BuildOrganization' table?

I am using SQL Server 2000.

Thanks.

 

[tayorolls]

Hi I got the first part solved. But I am trying to see if the 2nd question about the split is possible?

Select Min(Supervisor) FROM Test

It returns:

Choudhury, Ali

How can I split it into 2 columns?

So,

NextManagerFirstName = Ali
NextManagerLastName = Choudhury

Thanks.

 

[gmmastros]

Take a look at the following functions (in books on line)...

Left, Right, and CharIndex



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[nice95gle]

Ok, I was bored
You also have to check for names without a ','

Declare  @test TABLE(
LawsonID varchar(20) primary key,
FirstName varchar(100),
LastName varchar(100),
Supervisor varchar(100)
)

INSERT INTO @test(LawsonID, FirstName, LastName, Supervisor) Values ('10956',
'Ali','Chowdhury','Pidaparti, Purna')
INSERT INTO @test(LawsonID, FirstName, LastName, Supervisor) Values ('14533',
'Bradley','Rosintoski','Pidaparti, Purna')
INSERT INTO @test(LawsonID, FirstName, LastName, Supervisor) Values ('18778',
'Brian','Emmett','Pidaparti, Purna')
INSERT INTO @test(LawsonID, FirstName, LastName, Supervisor) Values ('21337',
'Sathiya','Rajagopal','Pidaparti, Purna')
INSERT INTO @test(LawsonID, FirstName, LastName, Supervisor) Values ('T2276',
'Aravind','Seshadri','Rajagopal, Sathiya')
INSERT INTO @test(LawsonID, FirstName, LastName, Supervisor) Values ('T2756',
'Polar','Huang','Rajagopal, Sathiya')
INSERT INTO @test(LawsonID, FirstName, LastName, Supervisor) Values ('T951',
'Rishi','Kabra','Rajagopal, Sathiya')
INSERT INTO @test(LawsonID, FirstName, LastName, Supervisor) Values ('T2396',
'Johnson','Abraham','Chowdhury, Ali')
INSERT INTO @test(LawsonID, FirstName, LastName, Supervisor) Values ('T716',
'Kevin','Schmidt','Chowdhury, Ali')
INSERT INTO @test(LawsonID, FirstName, LastName, Supervisor) Values ('24990',
'Purna','Pidaparti','Ileana Rivera')

select c.LawsonID, c.FirstName, c.LastName, c.Supervisor,
d.LawsonID as SupervisorID,
ltrim(Right(c.Supervisor,len(c.Supervisor)-charindex(',',c.Supervisor)))as SupervisorFirstName,
CASE WHEN charindex(',',c.Supervisor)=0 then 
	c.Supervisor
ELSE
	ltrim(Left(c.Supervisor,charindex(',',c.Supervisor)-1))
END as SupervisorLastName,
e.LawsonID as NextLevelManagerID,
ltrim(Right(d.Supervisor,len(d.Supervisor)-charindex(',',d.Supervisor)))as NextLevelManagerFirstName,
CASE WHEN charindex(',',d.Supervisor)=0 then 
	d.Supervisor
ELSE 
ltrim(Left(d.Supervisor,charindex(',',d.Supervisor)-1))
END as NextLevelManagerLastName
from @test c
left join @test d
on c.Supervisor=d.LastName+', '+d.FirstName
	left join @test e
		on d.Supervisor=e.LastName+', '+e.FirstName
order by c.LawsonID

Well Done is better than well said
- Ben Franklin

 

[tayorolls]

That was perfect. However since this query took 4 seconds for 1632 rows in the Query Analyzer, is there a way I store these results into a table which I can query from in ASP page?

Thanks.

 

[nice95gle]

Sorry I couldn't get back to you sooner.

Well from ASP you have a few options.

1. Temp table, But I bvelieve it only last as long as the procedure. You might not be able to access it after the proc is finished.
2. Table variable same as above.
4. Permanent table
3. an ADO recordset

from vbscript ADO would be something like this
Note: This was not tested.

Dim cnn 
Dim cmd 
Dim rs 

   Set cnn = CreateObject("ADODB.Connection")
    Set cmd = CreateObject("ADODB.Command")
        cnn.ConnectionString = "PROVIDER=sqloledb;data source=SERVER;UID=USER; PWD=PASSWORD; DATABASE=DATABASE;"

        cnn.Open
    cmd.ActiveConnection = cnn
    cmd.CommandText = "test"
  cmd.CommandType = adCmdStoredProc

Set rs = cmd.Execute

Do Until rs.EOF
    Debug.Print rs!colName
Loop

Well Done is better than well said
- Ben Franklin

 

[AlexCuse]

tayorolls-

Honestly, if you are concerned about performance you need to standardize your data. If you just change 'Ileana Rivera' to the right format in your sample data, and maybe use constraints to ensure that your rows don't reference supervisors that don't exist in your table, look how easy it will become:

'Fixed' Sample Data:

Declare  @test TABLE(
LawsonID varchar(20) primary key,
FirstName varchar(100),
LastName varchar(100),
Supervisor varchar(100)
)
declare @d datetime


INSERT INTO @test(LawsonID, FirstName, LastName, Supervisor) Values ('10956',
'Ali','Chowdhury','Pidaparti, Purna')
INSERT INTO @test(LawsonID, FirstName, LastName, Supervisor) Values ('14533',
'Bradley','Rosintoski','Pidaparti, Purna')
INSERT INTO @test(LawsonID, FirstName, LastName, Supervisor) Values ('18778',
'Brian','Emmett','Pidaparti, Purna')
INSERT INTO @test(LawsonID, FirstName, LastName, Supervisor) Values ('21337',
'Sathiya','Rajagopal','Pidaparti, Purna')
INSERT INTO @test(LawsonID, FirstName, LastName, Supervisor) Values ('T2276',
'Aravind','Seshadri','Rajagopal, Sathiya')
INSERT INTO @test(LawsonID, FirstName, LastName, Supervisor) Values ('T2756',
'Polar','Huang','Rajagopal, Sathiya')
INSERT INTO @test(LawsonID, FirstName, LastName, Supervisor) Values ('T951',
'Rishi','Kabra','Rajagopal, Sathiya')
INSERT INTO @test(LawsonID, FirstName, LastName, Supervisor) Values ('T2396',
'Johnson','Abraham','Chowdhury, Ali')
INSERT INTO @test(LawsonID, FirstName, LastName, Supervisor) Values ('T716',
'Kevin','Schmidt','Chowdhury, Ali')
INSERT INTO @test(LawsonID, FirstName, LastName, Supervisor) Values ('24990',
'Purna','Pidaparti','Rivera, Ileana')
INSERT INTO @test(LawsonID, FirstName, LastName, Supervisor) Values ('24997',
'Ileana','Rivera', NULL)

query:

select a.LawsonID
, a.FirstName
, a.LastName
, a.Supervisor
, b.LawsonID as SupervisorID
, b.FirstName as SupervisorFirstName
, b.LastName as SupervisorLastName
, c.LawsonID as NextLevelManagerID
, c.FirstName as NextLevelManagerFirstName
, c.LastName as NextLevelManagerLastName
from @test a
left join @test b
on a.Supervisor = b.LastName + ', ' + b.FirstName
left join @test c
on b.Supervisor = c.LastName + ', ' + c.FirstName
order by a.LawsonID

This gets rid of most of the string manipulation nonsense that you have to go through because of your crappy data, and I imagine would be MUCH faster (because string manipulation is one of the greatest weaknesses of T-SQL).

This is a time where you would be much better served to suck it up and take the time necessary to fix your database, as opposed to writing kludgy code that will be a nightmare to maintain the next time some nonsense finds its' way into your DB.

Please don't take this the wrong way, I just want to tell you that all the code work-arounds and subsequent optimizations in the world are not going to match the performance of a well-constructed database. We all get crappy data, and many have found out the hard way that fixing it now rather than implementing some work-around will make your life much easier down the road (myself included, I kick myself every day for crappy fixes like this that I made in the past)

Hope this helps,

Alex

ps - I couldn't test this, but here is a version of the same query (SQL 2005 only) using a Common Table Expression that may or may not be faster.

; --leave this semicolon
with SupervisorCTE as
(
select a.LawsonID as SupervisorID
, a.FirstName as SupervisorFirstName
, a.LastName as SupervisorLastName
, b.LawsonID as NextLevelManagerID
, b.FirstName  as NextLevelManagerFirstName
, b.LastName as NextLevelManagerLastName
from @test a
left join @test b
on a.Supervisor = b.LastName + ', ' + b.FirstName
)
select z.LawsonID, z.FirstName, z.LastName, z.Supervisor
, x.SupervisorID
, x.SupervisorFirstName
, x.SupervisorLastName
, x.NextLevelManagerID
, x.NextLevelManagerFirstName
, x.NextLevelManagerLastName
from @test z
left join SupervisorCTE x
on z.Supervisor = x.SupervisorLastName + ', ' + x.SupervisorFirstName
order by z.LawsonID

Ignorance of certain subjects is a great part of wisdom

 

[AlexCuse]

Also, it was only in reading my post that I realized how wide this thread is. In the future when you post code like this:

LawsonID,FirstName,LastName,DirectManagerLawsonID,

Try to include a space between column name and comma, like this:

LawsonID, FirstName, LastName, DirectManagerLawsonID,

This will give the site a chance to properly format the post, so it does not extend beyond the maximum screen width available (in ie6 anyway)

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom