sql server - SQL "if exists..." dynamic query

ID : 274548

viewed : 24

Tags : sqlsql-serverexistssql





Top 5 Answer for sql server - SQL "if exists..." dynamic query

vote vote

96

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  
vote vote

86

Try this:

DECLARE @Query NVARCHAR(1000) = 'SELECT @C = COUNT(*) FROM dbo.MyTable' DECLARE @Count AS INT EXEC sp_executesql @Query, N'@C INT OUTPUT', @C=@Count OUTPUT  IF (@Count > 0) BEGIN  END 
vote vote

74

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 @Query VARCHAR(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 :)

vote vote

63

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 @Query VARCHAR(1000) = 'SELECT * FROM dbo.MyTable',@hasRow int EXEC (@Query) SELECT @hasRow =@@ROWCOUNT // Returns the number of rows affected by the last statement  PRINT @hasRow   IF @hasRow > 0 BEGIN     Print 1 END BEGIN     Print 2 END 
vote vote

51

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; drop table if exists #temp go create table #temp (idCol int identity(1,1),someText nvarchar(1)) go insert into #temp values ('a') go 25000  declare @query nvarchar(max) ,@resultFork bit set @query = 'if exists (select * from #temp where idCol % 3 = 0)     set @resultFork=1     else     set @resultFork=0' print @query exec 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 */ 

Top 3 video Explaining sql server - SQL "if exists..." dynamic query







Related QUESTION?