question

I have a requirement where I have a deployment script for
sql server.

This is saved to a file test.sql

It is like this:

alter table [DB1].[dbo].[Table1] --drop column Col4
add Col4 varchar(100)
GO
insert into [DB1].[dbo].[Table1] values ('sdfs','fdsfew','sdrtyr','rtyrtyr');
GO

Now, I am trying to access the content of this file through a stored procedure and execute the script.

Below is the content for the stored procedure:

alter PROCEDURE dbo.usp_ExecuteScriptOnAllDB(
 
@in_filePath VarChar(260)=null,-- UNC paths seem to work fine here.
 
@out_contents nVarChar(1000)=null
)
AS
BEGIN
set @in_filePath ='C:\mssql\test.sql'
DECLARE @hr Int
DECLARE @o_fso Int, @o_file Int, @sz_contents VarChar(1000)
DECLARE @noErr Bit, @errMethod VarChar(255)
DECLARE @src VarChar(255), @desc VarChar(255)
SET @noErr = 1
EXEC @hr =sp_OACreate'Scripting.FileSystemObject', @o_fso OUT
IF @hr = 0
BEGIN
EXEC @hr =sp_OAMethod@o_fso,'OpenTextFile', @o_file OUT, @in_filePath, 1
IF @hr <> 0
BEGIN
EXECsp_OAGetErrorInfo@o_fso, @src OUT, @desc OUT
SET
@errMethod ='sp_OAMethod: OpenTextFile'
SET
@noErr = 0
END
END
IF @hr = 0
BEGIN
EXEC @hr =sp_OAMethod@o_file,'ReadAll', @sz_contents OUT
IF @hr <> 0
BEGIN
EXECsp_OAGetErrorInfo@o_fso, @src OUT, @desc OUT
SET
@errMethod ='sp_OAMethod: ReadAll'
SET
@noErr = 0
END
END
IF @hr = 0
BEGIN
EXEC @hr =sp_OADestroy@o_file
IF @hr <> 0
BEGIN
EXECsp_OAGetErrorInfo@o_fso, @src OUT, @desc OUT
SET
@errMethod ='sp_OADestroy: File Object'
SET
@noErr = 0
END
END
IF @hr = 0
BEGIN
EXEC @hr =sp_OADestroy@o_fso
IF @hr <> 0
BEGIN
EXECsp_OAGetErrorInfo@o_fso, @src OUT, @desc OUT
SET
@errMethod ='sp_OADestroy: FSO Object'
SET
@noErr = 0
END
END
SET @out_contents = @sz_contents
print @out_contents
SETNOCOUNTON;
EXECUTEsp_executesql@out_contents
END
GO

I am able to read and print the contents of the file.

But somehow it it not executing the script.

It says

Msg 102, Level 15, State 1, Line 5

Incorrect syntax near 'GO'.

Msg 102, Level 15, State 1, Line 9

Incorrect syntax near 'GO'.