I do not want to see the results of an empty result set.

I use dynamic execution of SQL.
During the writing of this question I came up a solution, so thanks for your
attention, a solution is at the end of this message. Maybe there is a more
elegant solution.
declare @.work varchar(8000)
set @.work = 'select * from an_example_table where x = 1234'
exec @.work
And I only want to see result sets which contain rows, I do not want to see
the sets not containing rows.
(I do not want to see the hundreds of empty selections, offcourse the string
for @.work is generated).
Is there an elegant and simple solution to this problem ?
Ben Brugman.
Things I have tried
The string in work is build dynamically. I do this for hundreds of tables.
But most tables do not return a result, a count(*) would return a 0 (zero).
If I do an exec of the string and pick up the @.@.row_count there is allready
some output, which I do not want.
I tried to use a variable to count the number of rows, but I can not get
this variable outside the exec statement.
set @.work = 'select @.counter = count(*) from an_example_table where x =
1234'
the above does not work
set @.work = 'declare @.counter int select @.counter = count(*) from
an_example_table where x = 1234'
But now @.counter can not be used to test upon.
If I do not use @.counter, the information of 'empty' result set get
returned.
Is there an elegant simple solution to this problem.
SOLUTION
During the typing I came up with the following working not so elegant
solutions :
set @.work2 = 'declare @.dummy int SELECT top 1 @.dummy = 3 from
an_example_table where x = 1234'
exec (@.work2)
set @.counter = @.@.rowcount
IF @.counter <> 0 DO SOMETHING
Thanks for your time and attention just formulating the question has brought
the anwser. You being there has been enough.Ben
declare @.x nvarchar(4000)
declare @.res int
set @.x= N'set @.res = (select count(*) from ' + N'northwind.dbo.customers
where customerid=''v''' +
')'
exec sp_executesql @.x, N'@.res int output', @.res = @.res output
if @.res >0
print 'yes'
"ben brugman" <ben@.niethier.nl> wrote in message
news:eiR198WOHHA.140@.TK2MSFTNGP04.phx.gbl...
>I use dynamic execution of SQL.
> During the writing of this question I came up a solution, so thanks for
> your attention, a solution is at the end of this message. Maybe there is a
> more elegant solution.
> declare @.work varchar(8000)
> set @.work = 'select * from an_example_table where x = 1234'
> exec @.work
> And I only want to see result sets which contain rows, I do not want to
> see the sets not containing rows.
> (I do not want to see the hundreds of empty selections, offcourse the
> string for @.work is generated).
> Is there an elegant and simple solution to this problem ?
> Ben Brugman.
> Things I have tried
> The string in work is build dynamically. I do this for hundreds of tables.
> But most tables do not return a result, a count(*) would return a 0
> (zero).
> If I do an exec of the string and pick up the @.@.row_count there is
> allready some output, which I do not want.
> I tried to use a variable to count the number of rows, but I can not get
> this variable outside the exec statement.
> set @.work = 'select @.counter = count(*) from an_example_table where x =
> 1234'
> the above does not work
> set @.work = 'declare @.counter int select @.counter = count(*) from
> an_example_table where x = 1234'
> But now @.counter can not be used to test upon.
> If I do not use @.counter, the information of 'empty' result set get
> returned.
> Is there an elegant simple solution to this problem.
> SOLUTION
> During the typing I came up with the following working not so elegant
> solutions :
> set @.work2 = 'declare @.dummy int SELECT top 1 @.dummy = 3 from
> an_example_table where x = 1234'
> exec (@.work2)
> set @.counter = @.@.rowcount
> IF @.counter <> 0 DO SOMETHING
> Thanks for your time and attention just formulating the question has
> brought the anwser. You being there has been enough.
>|||Thanks I used your implementation, which has the advantage of delivering the
actual count.
Thanks,
Ben
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:udEbrMXOHHA.3552@.TK2MSFTNGP03.phx.gbl...
> Ben
> declare @.x nvarchar(4000)
> declare @.res int
> set @.x= N'set @.res = (select count(*) from ' + N'northwind.dbo.customers
> where customerid=''v''' +
> ')'
> exec sp_executesql @.x, N'@.res int output', @.res = @.res output
> if @.res >0
> print 'yes'
>
>
> "ben brugman" <ben@.niethier.nl> wrote in message
> news:eiR198WOHHA.140@.TK2MSFTNGP04.phx.gbl...
>sql