Avoid showing results for querys with no results

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Other SQL Server Topics (2005)

Avoid showing results for querys with no results

Author

Topic

eshrauy
Starting Member

3 Posts

Posted - 2009-05-25 : 10:26:38

Hi,

I have a query that runs over many servers with the same db. Sometimes it doesn't find any rows matching with the criteria I want. So, How can I avoid showing query results when they are blank?

Thanks,

nr
SQLTeam MVY

12543 Posts

Posted - 2009-05-25 : 10:28:13

Just don't return a resultset?

What does the query look like.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

eshrauy
Starting Member

3 Posts

Posted - 2009-05-25 : 13:11:42

declare @query varchar(max)
declare @db_name varchar(100)

declare databases_cursor cursor FAST_FORWARD for
select [name] from QAXIT.[master].[dbo].[sysDatabases] where [name] like 'Server_%'

open databases_cursor

fetch next from databases_cursor into @db_name

while @@fetch_status = 0
begin

set @query = 'SELECT Shop.Name, WebAccessSiteID, Login, Password, UserWebAccessSite.Updated, UserWebAccessSite.Created, UserRole.RoleID
FROM QAXIT.' + @db_name + '.dbo.UserWebAccessSite
inner join QAXIT.' + @db_name + '.dbo.UserShop
ON (UserWebAccessSite.UserID = UserShop.UserID)
inner join QAXIT.' + @db_name + '.dbo.Shop
ON (UserShop.ShopID = Shop.ShopID)
inner join QAXIT.' + @db_name + '.dbo.UserRole
ON (UserRole.UserID = UserWebAccessSite.UserID)
WHERE Shop.Name like ''%Longhand%'' AND UserRole.RoleID = 2 order by UserWebAccessSite.Updated'

exec(@query)
fetch next from databases_cursor into @db_name
end

close databases_cursor
deallocate databases_cursor

nr
SQLTeam MVY

12543 Posts

Posted - 2009-05-25 : 13:15:49

create table #a (Name varchar(..), ....)

....
delete #a
insert #a
exec(@query)
if exists (select * from #a)
select * from #a

Or you could accumulate all the data in the temp table and return a single result set.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

eshrauy
Starting Member

3 Posts

Posted - 2009-05-25 : 14:29:20

Thanks, I added this to my query in this way:

declare @query varchar(max)
declare @db_name varchar(100)

declare databases_cursor cursor FAST_FORWARD for
select [name] from QAXIT.[master].[dbo].[sysDatabases] where [name] like 'Server_%'

open databases_cursor

fetch next from databases_cursor into @db_name

create table #auxTb (Name varchar(..), ....)

while @@fetch_status = 0
begin

set @query = 'SELECT Shop.Name, WebAccessSiteID, Login, Password, UserWebAccessSite.Updated, UserWebAccessSite.Created, UserRole.RoleID
FROM QAXIT.' + @db_name + '.dbo.UserWebAccessSite
inner join QAXIT.' + @db_name + '.dbo.UserShop
ON (UserWebAccessSite.UserID = UserShop.UserID)
inner join QAXIT.' + @db_name + '.dbo.Shop
ON (UserShop.ShopID = Shop.ShopID)
inner join QAXIT.' + @db_name + '.dbo.UserRole
ON (UserRole.UserID = UserWebAccessSite.UserID)
WHERE Shop.Name like ''%Longhand%'' AND UserRole.RoleID = 2 order by UserWebAccessSite.Updated'

insert #auxTb
exec(@query)

fetch next from databases_cursor into @db_name
end

select * from #auxTb
Drop Table #auxTb

close databases_cursor
deallocate databases_cursor

Now I could get all the results in just one table.

Thanks,
Eshra

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources