Migrating Stored procedure from SQL 2000

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 2008 Forums

Transact-SQL (2008)

Migrating Stored procedure from SQL 2000

Author

Topic

ladak
Starting Member

13 Posts

Posted - 2014-11-19 : 03:52:04

I everyone,

I have a stored procedure designed in sql server 2000, when i run this query it shows an error :

g 4147, Level 15, State 1, Procedure BalanceLotItems, Line 44
The query uses non-ANSI outer join operators ("*=" or "=*"). To run this query without modification,

here is syntax of stored procedure :

quote:

CREATE PROCEDURE BalanceLotItems

@Mill as varchar(30)
AS

SET NOCOUNT ON

Select
M.LotNo ,
Max(M.TransactionDate) as Date,M.DyeingMill as DyeingMill,
Sum(M.Quantity) as SentMeters,Sum(M.Pcs) as SentPcs, M.GreyQuality
Into #Temp1
From
IssueForDyeing M
Where
M.DyeingMill = Case When @Mill <> '' then @Mill else M.DyeingMill End
Group By M.DyeingMill,M.LotNo,M.GreyQuality

Select
T.LotNo,
Sum(T.GreyRecPcs) as ReceivedPcs,Sum(T.GreyRecMtrs) as ReceivedMtrs, T.GreyQuality
Into #Temp2
From
ReceiveDyeingTransaction T
Where
DyeingMill=@Mill
AND T.DyeingMill+T.LotNo in (Select DyeingMill+LotNo From #Temp1)
Group By DyeingMill,LotNo,GreyQuality

Select
Temp1.*,
Isnull(Temp2.ReceivedPcs,0) as ReceivedPcs, Isnull(Temp2.ReceivedMtrs,0) as ReceivedMeters,
Temp1.SentMeters - Isnull(Temp2.ReceivedMtrs,0) as BalMeters,Temp1.SentPcs - Isnull(Temp2.ReceivedPcs,0) as BalPcs
Into #Temp3
From
#Temp1 Temp1, #Temp2 Temp2

Where
Temp1.LotNo *= Temp2.LotNo And
Temp1.GreyQuality *= Temp2.GreyQuality

select
temp.LotNo LotNo , Convert(varchar(12),temp.Date,103) Date,Convert(varchar(15),temp.SentMeters) SentMeters,
Convert(varchar(15),temp.SentPcs) SentPcs,Convert(varchar(15),temp.ReceivedMeters) ReceivedMeters,
Convert(varchar(15),temp.ReceivedPcs) ReceivedPcs,Convert(varchar(15),temp.BalMeters) BalMeters,
Convert(varchar(15),temp.BalPcs) BalPcs,
temp.DyeingMill,C.ActName as MillName, temp.GreyQuality, G.GreyName, 0 as Status
from
#Temp3 temp , AcFile C , GreyQuality G
Where
temp.DyeingMill = C.ActCode And
temp.GreyQuality = G.GreyCode
Order By temp.LotNo,GreyQuality

GO

here you can see our company's previous programmer used *= in where clause, i have almost 37 stored procedures with same non-ANSI outer join operators ("*=" or "=*"), please guide me easiest way to replace *= with left outer join.

Thanks in advance.
Ladak

Ifor
Aged Yak Warrior

700 Posts

Posted - 2014-11-19 : 08:13:59

With the old outer join syntax, the * is against the table where all rows are preserved.

Here, as the order of tables in the FROM clause is the same as the order of the tables in the WHERE clause, you have a simple conversion to a LEFT OUTER JOIN:


SELECT ...
FROM #Temp1 T1
	LEFT JOIN #Temp2 T2
		ON T1.LotNo = T2.LotNo
			AND T1.GreyQuality = T2.GreyQuality;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources