How to use sp_configure in another Stored Procedure?

Question

I am trying to use Sp_configure Proc in another stored procedure, but getting errors.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE Test01
AS
BEGIN
    SET NOCOUNT ON;

    sp_configure 'show advanced options', 1
    RECONFIGURE
    GO
    sp_configure 'Ad Hoc Distributed Queries', 1
    RECONFIGURE
    Go
END
GO

The Error comes:-

Msg 102, Level 15, State 1, Procedure Test01, Line 6 Incorrect syntax near 'sp_configure'. Configuration option 'Ad Hoc Distributed Queries' changed from 1 to 1. Run the RECONFIGURE statement to install. Msg 102, Level 15, State 1, Line 1 Incorrect syntax near 'END'.

Answer 1

GO is not a TSQL command. It is a batch separator in the query window. The first GO after RECONFIGURE effectively ends the definition of your stored procedure. You also need to use EXEC when calling the procedures. See code below.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE Test01
AS
BEGIN
    SET NOCOUNT ON;

    EXEC sp_configure 'show advanced options', 1
    RECONFIGURE

    EXEC sp_configure 'Ad Hoc Distributed Queries', 1
    RECONFIGURE

END
GO