0

I've got several table with the column Archive. On a trigger I would like to check if the value exist in several tables.

Of course I can duplicate the query for each table but I think it could be easier to do this with a variable.

I've got a table RefTable with all the table created in the datase ID, TblName.

All the table are in the same database

Here is what I would like but it is not working :

IF EXISTS (SELECT archive from (select TblName FROM RefTable) WHERE archive = @var)
halfer
  • 19,824
  • 17
  • 99
  • 186
Chris
  • 927
  • 1
  • 8
  • 13
  • @RahulTripathi - Yes, so we can close it as a duplicate of all the other examples of this type of question. To do this will require dynamic SQL in some form - you can't use variables for table names (or any other identifier either). There are a number of examples on this site - please look for one that uses your specific RDBMS vendor (although most of these answers will be db-agnostic). – Clockwork-Muse Feb 27 '14 at 09:45

1 Answers1

0

You will certainly have to use some form of dynamic SQL to perform this search. An example (which you could pull into a function if you so desired):

declare @sqlstmt nvarchar(MAX)
declare @tablename nvarchar(100)
declare @username nvarchar(100)

set @tablename = 'UserProfile'
set @username = 'janed'
set @sqlstmt = 'select count(*) from ' + @tablename + ' where username = ''' + @username + ''''

exec sp_executesql @sqlstmt
user3358344
  • 193
  • 1
  • 9