Sql "if Exists..." Dynamic Query

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) =

Solution 1:

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

DECLARE@Query  NVARCHAR(1000) ='SELECT * FROM [dbo].[Mytable]',
        @rowcntINTEXEC Sp_executesql @querySELECT@rowcnt= @@ROWCOUNT

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

Solution 2:

Try this:

DECLARE@Query NVARCHAR(1000) ='SELECT @C = COUNT(*) FROM dbo.MyTable'DECLARE@CountASINTEXEC sp_executesql @Query, N'@C INT OUTPUT', @C=@Count OUTPUT

IF (@Count>0)
BEGINEND

Solution 3:

I know this answer is too late. but, I'm leaving this here to help someone else to use IF EXISTS with a dynamic query.

This is how you should do it with dynamic queries.

DECLARE@QueryVARCHAR(MAX)

SET@Query='SELECT * FROM [dbo].[MyTable]'SET@Query='IF EXISTS ('+@Query+')
                BEGIN
                    -- do something
                    print ''1''
                END
            ELSE
                BEGIN
                   -- do something else
                   print ''0''
                END
            'exec (@Query)

Hope this helped someone. Vote if it did :)

Solution 4:

You can use EXEC to execute sql statement, then call @@ROWCOUNT which Returns the number of rows affected by the last statement, to check row exists in sql select stetement.

DECLARE@QueryVARCHAR(1000) ='SELECT * FROM dbo.MyTable',@hasRowintEXEC (@Query)
SELECT@hasRow=@@ROWCOUNT//Returns the number ofrows affected by the last statement 
PRINT @hasRow 

IF @hasRow>0BEGIN
    Print 1ENDBEGIN
    Print 2END

Solution 5:

Hi I think that only way is to put IF EXISTS part into code of execution. My case is to stop execution in point when select affects at least one row, that is goal of IF EXISTS.

Little example that saves reading all records covering by condition to first occurence:

set nocount off;
droptable if exists #temp
go
createtable #temp (idCol intidentity(1,1),someText nvarchar(1))
go
insertinto #temp values ('a')
go 25000declare@query nvarchar(max)
,@resultFork bit
set@query='if exists (select * from #temp where idCol % 3 = 0)
    set @resultFork=1
    else
    set @resultFork=0'
print @queryexec sp_executeSQL @query, N'@resultFork int output', @resultFork=@resultFork output
print @resultFork/*Now U can use @resultFork in simple if condition...
if @resultFork = 1 
begin
    --
end
else 
begin
    --
end
*/