SQL "if exists..." dynamic query

sql sql-server exists

mayabelle · Dec 30, 2014 · Viewed 31k times · Source

Suppose I have a query stored in a variable like this (it's actually dynamically populated and more complex, but this is for demonstration purposes):

DECLARE @Query VARCHAR(1000) = 'SELECT * FROM dbo.MyTable'

Is there a way to check if the query would return any results? Something like this, but this doesn't work:

IF EXISTS (@Query)
BEGIN
    -- do something
END

The only way that I can think of to do this is to put the results in a temp table and then query from that, but that is not ideal because the columns in the dynamic query can vary and I really don't need the temp table at all for any reason other than checking whether some rows would be returned. Is there a better way?

Answer

Try Executing the Dynamic query and use @@RowCount to find the existence of rows.

DECLARE @Query  NVARCHAR(1000) = 'SELECT * FROM [dbo].[Mytable]',
        @rowcnt INT

EXEC Sp_executesql @query

SELECT @rowcnt = @@ROWCOUNT

IF @rowcnt > 0
  BEGIN
      PRINT 'row present'
  END

SQL "if exists..." dynamic query

Answer

Related questions