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?
The EXISTS operator is used to test for the existence of any record in a subquery. The EXISTS operator returns TRUE if the subquery returns one or more records.
Using CTEs, for instance, you can use SELECT from <subquery> in Open SQL. In my case I needed to execute dynamic SELECT count( DISTINCT col1, col2, …) which is not possible in the regular OpenSQL.
Dynamic SQL is a programming technique that enables you to build SQL statements dynamically at runtime. You can create more general purpose, flexible applications by using dynamic SQL because the full text of a SQL statement may be unknown at compilation.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With