I am passing dynamically generated query (stored in a variable) to exists() function but it is not working.
SET @TABLE ='MYTABLE'
SET @QUERY='SELECT * FROM '+@TABLE
IF(EXISTS(@QUERY)) PRINT 'RECORD EXISTS'
I have tried this too
--IF(EXISTS(EXEC(@QUERY))) PRINT 'RECORD EXISTS'
You cannot pas a variable like that to the exist funtion. All you can do is build dynamic sql and check if the rowcount after.
declare @table nvarchar(20)
declare @query nvarchar(200)
SET @TABLE = 'MYTABLE'
SET @QUERY = 'SELECT * FROM ' + @TABLE
EXECUTE sp_executesql @query
IF @@rowcount > 0
print 'record exists'
The problem with this method is that the resultset of @query will also be retrieved.
You can try fixing that like this :
declare @table nvarchar(20)
declare @query nvarchar(200)
SET @TABLE = 'MYTABLE'
SET @QUERY = 'if exists (SELECT * FROM ' + @TABLE + ') print ''record exists'''
EXECUTE sp_executesql @query
EDIT: the OP needs this in a function where you cannot call a procedure:
SQL Server does not allows calling procedures in a function.
A workaround can be to add a integer variable in you function (@tablenumber int) and use it like this :
if @tablenumber = 0
begin
if exists(select * from table1) print 'record exists'
end
else if @tablenumber = 1
begin
if exists(select * from table2) print 'record exists'
end
and so on
This will work if the number of tables is not to big. (it will still work but the maintenance needed is not worth it)
