Problem with Stored Procedure

I have a stored procedure from SQL 7 that won't run in SQL 2005, and I can't
figure out what the issue is. Can someone take a look at it and see if
anything stands out? The error message I get is: "Invalid column name: 'YR'.
Thanks so much!

@StartYear int,

@OnlyNew bit

As

set nocount on

declare @SQL as nvarchar(2000)

CREATE TABLE #temp1 (SOURCE varchar(255), YR Int, Amt float, CustCount INT)

CREATE UNIQUE INDEX MyIndex ON #temp1(Source, YR)

Set @SQL = 'INSERT INTO #temp1 (Source, YR, Amt)' +

'SELECT CUSTOMER.SOURCE, { fn YEAR(I.INVDATE) } AS YR, SUM(I.SDPRICE) AS Amt
' +

'FROM vwuniInvoiceDet I ' +

'INNER JOIN CUSTOMER ON I.INVCUST = CUSTOMER.[Index] '

If @OnlyNew =1 Set @SQL = @SQL + ' AND { fn YEAR(I.INVDATE) } = { fn
YEAR(dbo.CUSTOMER.MLDATE) } '

Set @SQL = @SQL + 'WHERE Customer.Source<>''XX'' And I.SDINVNUM Is Not Null
' +

'GROUP BY { fn YEAR(I.INVDATE) }, CUSTOMER.SOURCE ' +

'HAVING { fn YEAR(I.INVDATE) } >= ' + Convert(nvarchar, @StartYear) +

'ORDER BY Customer.Source, I.YR '

exec sp_executesql @SQL

.... (sp continues, but error is in the above section)

Neil
It throws an error proba;y on ORDER BY clause becvause I alias contains YR
alias as well
Itis really hard to test this query but what if you add { fn
YEAR(I.INVDATE) } to GROUP BY clause instead of I.YR
http://www.sommarskog.se/dynamic_sql.html







"Neil" wrote in message

>I have a stored procedure from SQL 7 that won't run in SQL 2005, and I
>can't figure out what the issue is. Can someone take a look at it and see
>if anything stands out? The error message I get is: "Invalid column name:
>'YR'. Thanks so much!
>
> @StartYear int,
>
> @OnlyNew bit
>
> As
>
> set nocount on
>
> declare @SQL as nvarchar(2000)
>
> CREATE TABLE #temp1 (SOURCE varchar(255), YR Int, Amt float, CustCount
> INT)
>
> CREATE UNIQUE INDEX MyIndex ON #temp1(Source, YR)
>
> Set @SQL = 'INSERT INTO #temp1 (Source, YR, Amt)' +
>
> 'SELECT CUSTOMER.SOURCE, { fn YEAR(I.INVDATE) } AS YR, SUM(I.SDPRICE) AS
> Amt ' +
>
> 'FROM vwuniInvoiceDet I ' +
>
> 'INNER JOIN CUSTOMER ON I.INVCUST = CUSTOMER.[Index] '
>
> If @OnlyNew =1 Set @SQL = @SQL + ' AND { fn YEAR(I.INVDATE) } = { fn
> YEAR(dbo.CUSTOMER.MLDATE) } '
>
> Set @SQL = @SQL + 'WHERE Customer.Source<>''XX'' And I.SDINVNUM Is Not
> Null ' +
>
> 'GROUP BY { fn YEAR(I.INVDATE) }, CUSTOMER.SOURCE ' +
>
> 'HAVING { fn YEAR(I.INVDATE) } >= ' + Convert(nvarchar, @StartYear) +
>
> 'ORDER BY Customer.Source, I.YR '
>
> exec sp_executesql @SQL
>
> ... (sp continues, but error is in the above section)
>
>
>
>
>