IF statement execution dependant on record count
Posted on 2006-06-18
I want to perform a series of actions on a table, but only if there are records in the table.
The table name will eventually be passed as parameter to the stored procedure, so the table name in the SELECT COUNT statement can change.
I'm struggling with the syntax and would appreciate some help. I'm getting the following error:
Server: Msg 137, Level 15, State 2, Line 17
Must declare the variable '@tableName'.
I have declared the variable so I must be doing something else wrong. Does anyone have any suggestions on how to fix this, or perhaps a better way to achieve the same thing?
Code being used:
DECLARE @type int, @tableName varchar(200), @numRecords int
SET @type = 7
IF @type = 7
SET @tableName = 'table1'
SET @tableName = 'table2'
SELECT @numRecords= COUNT(*) FROM @tableName
-- only try and transfer records if some exist
IF (@numRecords) > 0
PRINT 'There are records in the table'