1

If I execute the following SQL statement in SQL server 2008 it works perfectly, but when execute the same statement in SQL Server 2000 it doesn't work:

Statement: 

select top 1  
  [k].[FixbiUnitPrice] 
from  (
  select top (select COUNT(*) 
              from [dbo].[mnrFnBI_Fixed]('4E591E71-33BD-4ECC-8703-771BE8A76817') f) 
    [FixbiUnitPrice],
    BDate,
    biNumber 
  From [dbo].[mnrFnBI_Fixed]('4E591E71-33BD-4ECC-8703-771BE8A76817') f  
  where f.BAccCustID != 0x0 
  order by f.BDate desc,f.BNumber desc,f.biNumber desc
) [k]

Output in SQL Server 2000:

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '('.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'FixbiUnitPrice'.

What's wrong with syntax for SQL Server 2000

T I
  • 9,785
  • 4
  • 29
  • 51
Wissam A Jackal
  • 23
  • 7
  • It does not work like that. a `top x` expression can not be dynamic. – juergen d May 17 '15 at 13:46
  • thanks for the fast replay , so how can i fix the above statement to work with sql 2000 ?! what other way can it be done ?! – Wissam A Jackal May 17 '15 at 13:49
  • I am not sure about SQL Server 2000 but try computing the `TOP` into a variable and then using the variable in the query. `DECLARE @n INT SET @n = (SELECT COUNT(*) FROM ...) ... SELECT TOP (@n) ...` – T I May 17 '15 at 13:55
  • @TI [This shouldn't work on sql server 2000.](https://msdn.microsoft.com/en-us/library/aa259187(v=sql.80).aspx#_select_clause) – Zohar Peled May 17 '15 at 14:06

2 Answers2

1

Support for 'dynamic' top in sql server started in 2005 version. for sql server 2000 you can only use a constant number after top. you can probably use SET ROWCOUNT for your query.

Also, Read this post and it's answers.

Community
  • 1
  • 1
Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
1

In addition to @Zohar's point that a variable TOP @N isn't supported in Sql 2000, what you could also do is generate Dynamic Sql and then execute it, i.e.:

DECLARE @TopCount INT
DECLARE @Sql NVARCHAR(2000)

SELECT @TopCount = COUNT(*) 
FROM [dbo].[mnrFnBI_Fixed]('4E591E71-33BD-4ECC-8703-771BE8A76817')

SET @Sql = 
'select top 1 
  [k].[FixbiUnitPrice] 
from  (
  select top ' + CONVERT(NVARCHAR(50), @TopCount) + '
    [FixbiUnitPrice],
    BDate,
    biNumber 
  From [dbo].[mnrFnBI_Fixed](''4E591E71-33BD-4ECC-8703-771BE8A76817'') f  
  where f.BAccCustID != 0x0 
  order by f.BDate desc,f.BNumber desc,f.biNumber desc
) [k]'

EXEC sp_executesql @Sql

That said, Sql 2000 is really an unsupported, legacy technology and you need to consider upgrading to a more recent version of Sql Server as soon as possible.

Andriy M
  • 76,112
  • 17
  • 94
  • 154
StuartLC
  • 104,537
  • 17
  • 209
  • 285
  • Good point about 2000 not being supported. However I would be careful to advise the use of dynamic sql without mentioning sql injection. – Zohar Peled May 18 '15 at 03:31
  • @ZoharPeled while in general, you are correct when dealing with string data coming from untrusted user input, you will however note that 1) `@TopCount` is a strongly typed INT, not a string, and 2) That `@TopCount` is derived by ourselves in the line above. So in this case, no fear. – StuartLC May 18 '15 at 03:45
  • I accept that. However, most people tend to simplify the problem when introducing it on stackoverflow, so it's quite possible that the actuall query that the OP needs has some variables that the user supplied. Also, peopel tend to take a solution that works for something and extand it to other things as well, meanin that perhaps the OP will try to convert some other statements to dynamic sql. This is why whenever I mention the possibility of using dynamic sql I also mention little bobby tables. – Zohar Peled May 18 '15 at 03:58
  • thanks a lot guys that solved my problem , i'm using sql 2000 also sql 2008 r2 , i have to make sure that the procedure i create in sql 2008 works on sql 2000 also .. anyway thanks for the good advice and thanks for help . – Wissam A Jackal May 18 '15 at 06:43