Executing a stored procedure in dynamic SQL

Stored Procedures can be executed with a command or, using sp_executeSQL and the defined procedure in the context of the database.

Here is a stored procedure in the pubs database (just the name of the database in the instance of SQL Server 2016 is named TestSQL. It has everything that pubs has except for the name).

Well, pubs database may not be available on SQL Server 2016 and you can use any other database.

I am jsut taking the example of the stored procedure as shown here:
---------------

USE [TestPubs]
GO

/****** Object:  StoredProcedure [dbo].[reptq2]    Script Date: 8/17/2016 11:37:46 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[reptq2] AS
select
    case when grouping(type) = 1 then 'ALL' else type end as type,
    case when grouping(pub_id) = 1 then 'ALL' else pub_id end as pub_id,
    avg(ytd_sales) as avg_ytd_sales
from titles
where pub_id is NOT NULL
group by pub_id, type with rollup
GO
---------------------
The parameter is the return integer. There is no IN parameter.

The following statement executes the stored procedure as a dynamic sql.

---------------------
declare @proc nvarchar(25);
set @proc='dbo.reptq1'
--exec @proc
exec sp_executeSQL @proc
--------------

Sometimes instead of the procedure being defined with the schema as in the above, it could be just the name of the procedure. However, the statement needs to be executed in the context of the database. There are no input parameters and if there are any, they should be declared and set.