CODEWITHSUNDEEP
sql-serversql-server-2008
user2135970
user2135970

Reputation: 815

How Do I Use a Variable in a Select Statement?

When I run the following code I get the error : Incorrect syntax near '@num_to_remove'.

Any idea why this doesn't work?

Thanks in advance, Matt

DECLARE @num_to_remove INT
SET @num_to_remove = 2

-- get em_ids for records to delete
WITH em_ids
 AS (SELECT TOP @num_to_remove em_id
       FROM irs_self_cert_em sc
      WHERE     sc.date_cert_loc IS NULL
            AND sc.date_first_cert_email_sent < '2014-10-03')

SELECT * FROM em_ids

Upvotes: 0

Views: 61

Answers (2)

Thit Lwin Oo
Thit Lwin Oo

Reputation: 3438

Try this (Note: I haven't tested it. You can try similar this)

DECLARE @num_to_remove INT
SET @num_to_remove = 2

-- get em_ids for records to delete
EXEC
(
    'WITH em_ids
    AS (SELECT TOP ' + CAST(@num_to_remove AS varchar(10)  + 'em_id
       FROM irs_self_cert_em sc
      WHERE     sc.date_cert_loc IS NULL
            AND sc.date_first_cert_email_sent < ''2014-10-03'')
    '
)

Upvotes: 2

Tab Alleman
Tab Alleman

Reputation: 31775

In regular tSQL, you can only use variables to represent values, not column names or other objects. I know the numeric value for a "TOP" clause seems like it should qualify but it doesn't.

To use a variable in this way, you have to do Dynamic SQL.

Upvotes: 3

Related Questions