How can we use EXEC sp_executesql for two dynamic SQL statements?

tables:

create table TabA
             (ID int, Name varchar(20))
             insert into  TabA
             select 1,'ABC' union
             select 2,'DEF' union
             select 3,'GHD' 

create table TabB
             (ID int, Name varchar(20))
             insert into  TabA
             select 1,'XYZ' union
             select 2,'STF' union
             select 3,'LDZ' 

create table status
             (Result1 int,Result2 int )

Create table query(query1 varchar(1000),query2  varchar(1000))

             Insert into query(query1,query2)
             select  '''select COUNT(*) from TabA''','''select COUNT(* )from TabB'''
             select * from query

procedure:

create  Procedure [dbo].spStatus
AS
BEGIN

  SET NOCOUNT ON;


Declare  @sqlString1 nvarchar(1000)
    ,@sqlString2 nvarchar(1000)
     ,@col_value1 varchar(256) 
    ,@col_value2 varchar(256)

   select @sqlString1 = query1
         , @sqlString2 =query2         

      from Query


        EXEC sp_executesql                       

         @query=@sqlString1, --sql string is your full select  statement                       

           @params = N'@col_Value1 varchar(256)  OUTPUT',                

            @col_Value1 = @col_Value1 OUTPUT
           print(@sqlString1)

           --  @sqlString2, --sql string is your full select  statement                       

           --@par开发者_运维百科ams = N'@col_Value2 varchar(256)  OUTPUT',                

           -- @col_Value2 = @col_Value2 OUTPUT


        Insert Into dbo.Status(Result1,Result2 )
        Values(@col_Value1,@col_Value2)



End

It works if we use @query=@sqlString1 only but I want both statement @query=@sqlString1,@query=@sqlString2 should execute together.

Please help how can we use both statement to execute?

Thanks in Advance

Did you mean:

SET @sqlString1 = @sqlString1 + ';' + @sqlString2;

EXEC sp_executesql @query = @sqlString1 --...

Concat the two queries together with a + (@query=@sqlString1 + '; ' + @sqlString2)

Then use two variables to capture the two counts into output variables

OR

Insert into query(query1,query2)
    EXEC  sp_executesql 'SELECT ( select COUNT(*) from TabA ) AS query1, ( select COUNT(*)from TabB )  AS query2'

... but really and truly dynamic SQL isn't needed for that at all.

try this:

--add this
DECLARE @SQL nvarchar(max)
SET @SQL=ISNULL(@sqlString1,'')+';'+ISNULL(@sqlString2,'')

--change this
EXEC sp_executesql @query=@SQL
    ,@params = N'@col_Value1 varchar(256)  OUTPUT'
    ,@col_Value1 = @col_Value1 OUTPUT