CODEWITHSUNDEEP
sqlt-sqlsql-server-2000
pvzkch
pvzkch

Reputation: 341

Failed to compare two TSQL variables

I am having a little problem with comparing two variables in TSQL.

Here's the code

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sspUpdateActivityDate] (
    @e nvarchar(15),
    @i nvarchar(11),
    @c nvarchar(50), 
    @u nvarchar(50)
)
AS
BEGIN
    DECLARE @var_Count int
    DECLARE @var_RecordLimit int

    SET @var_RecordLimit = 20

    SELECT @var_Count = COUNT(*) FROM tblLastAccess WHERE employeeID = @e
    IF @var_Count > @var_RecordLimit
        BEGIN
            DELETE FROM tblLastAccess
                WHERE lastDate NOT IN (
                    SELECT TOP @var_RecordLimit lastDate
                    FROM tblLastAccess
                    WHERE employeeID = @e
                    ORDER BY lastDate DESC
                )
        END
    INSERT INTO tblLastAccess(employeeID, ip, computerName, username) VALUES (@e, @i, @c, @u)
END

Error says

Msg 102, Level 15, State 1, Procedure sspUpdateActivityDate, Line 19
Incorrect syntax near '@var_RecordLimit'.

Which points after the comparison between @var_Count and @var_RecordLimit (as far as I understand it)

The idea is to add records but limit the record count dynamically, which I set initially to 20. The code block inside the IF is to delete the records next to TOP n values.

I am using SQL Server 2000 by the way.

Please guide me.

It is for update purposes by the way, that is why I am using ALTER and not CREATE

Upvotes: 1

Views: 84

Answers (2)

shree.pat18
shree.pat18

Reputation: 21757

You cannot use a variable in the TOP clause this way; you must use a constant value there. You would typically use dynamic SQL to do this kind of operation, where your query is dependent on variable values as in your example. So, you can do something like this:

DECLARE @SQL NVARCHAR(1000)
SET @SQL = 'DELETE FROM tblLastAccess WHERE lastDate NOT IN ('
           +'SELECT TOP'
           + cast(@var_RecordLimit as varchar)
           + ' lastDate FROM tblLastAccess WHERE employeeID = @empID ORDER BY lastDate DESC)'

EXECUTE sp_executesql @SQL, N'@empID nvarchar(15)', @e

Note that since @e is an input parameter, it opens up the dynamic query to SQL Injection, so we replace it with another parameter @empID within the dynamic query, and then pass the actual value when executing it.

Upvotes: 2

Mudassir Hasan
Mudassir Hasan

Reputation: 28751

Just enclose the variable in parenthesis and it will work fine

SELECT TOP (@var_RecordLimit) lastDate
FROM tblLastAccess
WHERE employeeID = @e
ORDER BY lastDate DESC

Upvotes: 2

Related Questions