asked
How to check table is empty or not if query is generated dynamically
Hi All, DECLARE @a NVARCHAR(MAX) DECLARE @a Cnt Int DECLARE @tableName NVARCHAR(MAX) SET @Cnt = 5 WHILE @Cnt >0 BEGIN SET @tableName = (SELECT tableName from TableContainsAllTableName WHERE ID = Cnt) SET @a = 'SELECT * FROM '+@tableName+' -- Here I want to check that before executing above query data is present in above table or not -- i.e is @tableName table Empty if yes then don't show result if no then show result EXECUTE (@a) Need Help ASAP
You could add a check across to sys.partitions to get a rowcount so your @tablename select would look something like... SELECT tableName from TableContainsAllTableName WHERE ID = Cnt and tablename in ( select name, (select sum(rows) from sys.partitions p where p.object_id = o.object_id) as numrows from sys.objects o where type = 'U' and (select sum(rows) from sys.partitions p where p.object_id = o.object_id) > 0 ) and then check if @tablename has a value before executing the dynamic sql
Why not use `IF EXISTS (SELECT top 1 * FROM ' + @TABLENAME` in your @a ? What is the other part of the logic - what if there are no rows?
declare @strSQLN nvarchar(100) declare @paramDefn nvarchar(100) declare @RowCount int set @strSQLN=N'select @RowCountOUT=count(*) from '+@tableName+' (NOLOCK) ' set @paramDefn=N'@RowCountOUT int output' exec **sp_executesql** @strSQLN, @paramDefn, @RowCountOUT=@RowCount output print @RowCount
