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

If you don't want a result set returned, why do you have a `SELECT` in it? Amend the SQL to not return a result set, and you don't get a result set. This smells like an [XY Problem](http://xyproblem.info). — Thom A, Aug 04 '21 at 08:35
*"This isn't working for me `SET NOEXEC ON` because it just compile not execute "* That's what `NOEXEC` is supposed to do. From [SET NOEXEC (Transact-SQL)](https://learn.microsoft.com/en-us/sql/t-sql/statements/set-noexec-transact-sql?view=sql-server-ver15): *"Compiles each query but does not execute it."* — Thom A, Aug 04 '21 at 08:38
You can just add variable @debug in your procedure and if execute with parameter =1 only show selects inside — Adamszsz, Aug 04 '21 at 08:38
I am using select into below query . @Larnu 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 ' — Hakim Chandio, Aug 04 '21 at 08:51
@HakimChandio [edit] your question to include attempts; don't put them in the comments. — Thom A, Aug 04 '21 at 08:52
Also, I hope the columns you are injecting into that query are properly delimit identified. — Thom A, Aug 04 '21 at 08:54
So, the above statement *won't* return a data set, due to the `INTO`. — Thom A, Aug 04 '21 at 08:54
Start at the beginning: what are you actually trying to do? You haven't shown us your full code, where does `##temp` come from? — Charlieface, Aug 04 '21 at 10:44

Adamszsz · Answer 1 · 2021-08-04T09:13:50.493

0

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

edited Aug 04 '21 at 09:13

answered Aug 04 '21 at 08:45

Adamszsz

561
3
13

But its necessary to execute sp_executesql because after that my data save in ##temp then i will further play with data hope you understand . @Adamszsz – Hakim Chandio Aug 04 '21 at 09:05
Edited answear , you can create later Temp table , and then do insert into with executed procedure - i think that was what you looking for - there is idea only .You need to to do with your data just. And ofc - with debug = 0 the procedure will not return now data :P . Let me know if it helps you or i can delete just answear – Adamszsz Aug 04 '21 at 09:14

Hide Result set of sp_executesql

1 Answers1