Join stored procedure help

[anorthcote]

Hi,

I have a stored procedure to get some call information from a couple of databases.

I want to join two tables on the column OrderID, the problem I have is that on the ContractorOrder table OrderID is an INT and on Calls it is a VARCHAR.

What I want to do is to limit the join to only look for a match when it is numeric but still list the text entries on Calls whatever the type.



CREATE PROCEDURE CallsDetail

@LogID int AS

BEGIN

SELECT TOP 100 PERCENT dbo.Calls.LogID, dbo.Calls.EnteredBy, dbo.Calls.Entered, dbo.Calls.LocationID, SZ.dbo.Location.Branch,
CAST(SUBSTRING(SZ.dbo.Area.Name, 6, 2) AS int) AS Area, dbo.Calls.ProblemText, dbo.Calls.OrderID,
SZ.dbo.Contractor.Name AS Contractor, dbo.Calls.DealtWith, dbo.Calls.FixBy, dbo.Calls.DateCompleted, dbo.Calls.ConfirmedBy,
dbo.Calls.StatusID, dbo.Calls.Comments
FROM SZ.dbo.Contractor INNER JOIN
SZ.dbo.ContractorOrder ON SZ.dbo.Contractor.ID = SZ.dbo.ContractorOrder.ContractorID RIGHT OUTER JOIN
dbo.Calls ON SZ.dbo.ContractorOrder.OrderID = dbo.Calls.OrderID LEFT OUTER JOIN
SZ.dbo.Area RIGHT OUTER JOIN
SZ.dbo.Location ON SZ.dbo.Area.ID = SZ.dbo.Location.AreaID ON dbo.Calls.LocationID = SZ.dbo.Location.Code


where LogID = @LogID


END

GO

I hope someone can help.

Cheers

 

[SQLSister]

I'm not at all sure what you are asking for. Could you give some sample data and sample results?

On a side note, I know I personally find it much easier to follow a script if you stick consistently to left joins or rights joins not mix them in the same query.

"NOTHING is more important in a database than integrity." ESquared

 

[chrissie1]

Something is wrong with the design if OrderId is varchar in one table and int the other. How did you set the FK-constraints on that?

Christiaan Baes
Belgium

My Blog

 

[anorthcote]

Ok, I've sorted it...

SELECT TOP 100 PERCENT dbo.Calls.LogID, dbo.Calls.EnteredBy, dbo.Calls.Entered, dbo.Calls.LocationID, SZ.dbo.Location.Branch,
CAST(SUBSTRING(SZ.dbo.Area.Name, 6, 2) AS int) AS Area, dbo.Calls.ProblemText, dbo.Calls.OrderID,
SZ.dbo.Contractor.Name AS Contractor, dbo.Calls.DealtWith, dbo.Calls.FixBy, dbo.Calls.DateCompleted, dbo.Calls.ConfirmedBy,
dbo.Calls.StatusID, dbo.Calls.Comments
FROM SZ.dbo.Contractor INNER JOIN
SZ.dbo.ContractorOrder ON SZ.dbo.Contractor.ID = SZ.dbo.ContractorOrder.ContractorID RIGHT OUTER JOIN
dbo.Calls ON SZ.dbo.ContractorOrder.OrderID = case when isnumeric(dbo.Calls.OrderID) = 0
then null else dbo.Calls.OrderID end LEFT OUTER JOIN
SZ.dbo.Area RIGHT OUTER JOIN
SZ.dbo.Location ON SZ.dbo.Area.ID = SZ.dbo.Location.AreaID ON dbo.Calls.LocationID = SZ.dbo.Location.Code


I didn't know SQL had ISNUMERIC!!!