Linking Issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Linking Issue

Author

Topic

mason
Starting Member

11 Posts

Posted - 2003-06-03 : 16:50:42

I have the following stored proc but I wanted to find out if there might be a better way to optimize it:

CREATE PROCEDURE TEST AS
SELECT * FROM JOHN.dbo.TEST1, JOHN.dbo.TEST2
Where JOHN.dbo.TEST1.FirstName + JOHN.dbo.TEST1.LastName = JOHN.dbo.TEST1.Cust_Name

What I'm trying to do is figure out how to return the records from TEST1 and TEST2 which are supposed to be linked on Customer Name.

Table 1 has Customer First and Last Name while Table 2 only has Customer Name. Trying to figure out how to best approach this.

This is my second issue we've run into since the weekend and I think I'm close with the statement I've compiled above. Any help would be greatly appreciated.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-06-03 : 17:03:23

Are you sure theres no separator between the names
JOHN.dbo.TEST1.FirstName + ' ' + JOHN.dbo.TEST1.LastName
or
JOHN.dbo.TEST1.FirstName + ', ' + JOHN.dbo.TEST1.LastName

Not really a lot you can do with it
You could add
and left(JOHN.dbo.TEST1.Cust_Name,len(JOHN.dbo.TEST1.FirstName) = JOHN.dbo.TEST1.FirstName
and right(JOHN.dbo.TEST1.Cust_Name,len(JOHN.dbo.TEST1.LastName) = JOHN.dbo.TEST1.LastName

which might allow it to use an index on JOHN.dbo.TEST1.FirstName or JOHN.dbo.TEST1.LastName
but it probably won't make much difference.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources