How to return only a single resultset in a stored procedure

Question

Consider the following stored procedure :

create procedure [dbo].[MyTest] ( @p_SqlStatement nvarchar(max) )
as
begin

    exec sp_executesql @p_SqlStatement

    if @@ROWCOUNT = 1
    begin
      select 1;
    end
    else if @@ROWCOUNT <> 1
    begin
      select 0;
    end

end

This stored procedure currently returns 2 datasets, one with the exec sp_executesql @p_SqlStatement data, and the other one would be either 1 or 0. Is there a way to suppress the first dataset? I mean, would it be possible that this stored procedure returns only 1 or 0 ?

I tried adding a RAISERROR( 'MyError', 18, 1 ) right after the exec sp_executesql @p_SqlStatement and then in the catch block select something else, but the first result set is always returned to my stored procedure caller...

Answer 1

You can embed the query in an if exists(.

alter procedure [dbo].[MyTest] ( @p_SqlStatement nvarchar(max) )
as
begin
    set @p_SqlStatement = 'if exists('+@p_SqlStatement+') select 1 else select 0'

    exec sp_executesql @p_SqlStatement
end

There are however some queries where this won't fly.

• Multiple statements
• Queries terminated with ;
• Queries that uses CTE's

There might be more but these are the ones I can think of right now.

Update:

You could try to use openrowset.

alter procedure [dbo].[MyTest] ( @p_SqlStatement nvarchar(max) )
as
begin
  declare @S nvarchar(max)
  set @S = 
    'if exists(
              select *
              from openrowset(
                              ''SQLNCLI'',
                              ''Server=localhost;Trusted_Connection=yes;'',
                              '+quotename(@p_SqlStatement, '''')+'
                             ) as T
              )
      select 1
    else
      select 0'

  exec (@S)
end

I have never used this in productions but from the tests I made i looks like it should work with SP's, CTE's and multiple lines.

You have to allow ad hoc distributed queries.

Answer 2

You may try the NOCOUNT statement ( http://msdn.microsoft.com/en-us/library/ms189837.aspx )

create procedure [dbo].[MyTest] ( @p_SqlStatement nvarchar(max) )
as
begin

SET NOCOUNT ON;

    exec sp_executesql @p_SqlStatement

    if @@ROWCOUNT = 1
    begin
      select 1;
    end
    else if @@ROWCOUNT <> 1
    begin
      select 0;
    end

SET NOCOUNT OFF;

end

Answer 3

try this approach:

declare @mycount bigint

exec sp_executesql N'select @mycount = count(name) from Page where name like ''P%''', N'@mycount bigint OUTPUT', @mycount OUTPUT

select @mycount

It is important that your statement @p_SqlStatement incorporates a count.

If that is not the case, meaning your want to run this sp for any SQL you come upon, then this is no help. I don't think your can't suppress output of the sp_executesql.

Edit: You could also try this:

declare @mycount bigint

exec sp_executesql N'SELECT * INTO ##MyTempTable from Page where name like ''P%'''

select count(*) from ##MyTempTable 
drop table ##MyTempTable 

This means that you will have to add the following to every query (don't know if this works with sp's?) "SELECT * INTO ##MyTempTable FROM " - That shouldn't be to hard.

"##temptables" are global scoped temptables. Which means that they are also available outside the sp_executesql sp. You must drop the table explicitly.

Answer 4

I was not able to find any other workaround than using the suggested OPENROWSET. However, I found a way to be independant from the servername / instance. I still have to reconfigure the server to accept ad hoc distributed queries. Here is the final result :

create procedure [dbo].[MyTest] ( @p_SqlStatement nvarchar(max) )
as
begin

    declare @sql nvarchar(max) = N'SELECT * INTO ##TMP FROM OPENROWSET(''SQLOLEDB'',''Server=' + @@SERVERNAME + ';Trusted_Connection=Yes;'',''' + @p_SqlStatement + ''')';
    exec sp_executesql @sql

    if ( select COUNT(1) from ##TMP ) = 1
    begin
      select 1;
    end
    else
    begin
      select 0;
    end

    drop table ##TMP;

end

This solution has it's limitations :

1. Every column in the @p_SqlStatement must have a name
2. I have to enable Ad Hoc Distributed Queries on my server.
3. I have to use @sql variable along with sp_executesql on the OPENROWSET because I was not able to use variables inside OPENROWSET in another way, so this makes dynamic SQL on an OPENROWSET, which is pretty bad performance-wise.

I do reconfigure the server using the following script :

sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO