Hide Result set of sp_executesql

Question

I am trying to run sp_executesql but I don't want to show the results in resultset and I can't use variables because my stored procedure calls different datasources. So I am not able to create single table what should I do? Any suggestions?

--INSERT INTO #TEMP  this what i want but didn't work i know

EXECUTE sp_executesql  @SQL,  N'@ENTITYID INT,@REPORTINGDATE DATE,@PREVIOUSREPORTINGDATE DATE',   
                                @ENTITYID    ,@REPORTINGDATE     ,@PREVIOUSREPORTINGDATE 

                
SET @tempSQL = 'SELECT '+@Select+' INTO #TEMP FROM ##TEMP '+@GROUPBY+' DROP TABLE ##TEMP '              
                    

This isn't working for me SET NOEXEC ON because it just compile not execute and in my query I am using ##temp.

This is my query

select A , B , SUM(C)
into ##temp
FROM
        #tempp
GROUP BY
        A , B 

select * from ##temp

enter image description here don't want first result set

Answer 1

You can add parameter like @debug inside your procedure and do selects only if you execute procedure with @debug =1

What i mean you procedure should looks like :

ALTER PROCEDURE [dbo].[History]
(
@Id_user NVARCHAR(450),
@debug int
)
AS

BEGIN
SET NOCOUNT ON


begin tran az


if(@debug) =1
begin
select 'A', 'B' from yourtable where id = @Id_user
end


commit tran az

 
END

and execute procedure with debug = 0 will not show your select .

DECLARE @return_value int

EXEC    @return_value = [dbo].[History]
        @Id_user = '122',
        @debug  =0

And later you can play with you data like that :

DECLARE @tempTable  TABLE(
            ID_user  VARCHAR(100)
            
    )
    

  INSERT INTO @tempTable 
  EXEC  @return_value = [dbo].[History]
        @Id_user = '122',
        @debug  =0


        select * from @tempTable