Is it possible to get the content of a script file in a database project?

Question

Ok so I have a Visual Studio Database project where I´m trying to do some post deployment stuff (Script.PostDeployment.sql).

What am I trying to do?

What I like to do is to be able to copy the content of a script file and insert it into a procedure.

Overview

So I have a file with some SQLCMD

MyScriptFileWithSomeInserts.sql -- The content I like to log is in this file

exec InsertDataProcedure [ScriptContent] -- How can I get it here?

Here we have some pseudocode of what I'm trying to do

-- read file and insert to variable
:setvar ScriptContent :r MyScriptFileWithSomeInserts.sql 

-- input the content to the procedure
exec InsertDataProcedure [@ScriptContent] 

What have I tried?

1

The following fails because this is not a procedure but just a script in my database project.

declare @ScriptContent nvarchar(Max)
SELECT @ScriptContent = OBJECT_DEFINITION(OBJECT_ID(N'MyScriptFileWithSomeInserts.sql'))
exec InsertDataProcedure @ScriptContent 

2

I have tried to use :r (SQLCMD) to read the file

declare @xx nvarchar(Max) =
:r MyScriptFileWithSomeInserts.sql

exec InsertDataProcedure [@ScriptContent] 

3

I have tried to use all kinds of variations of SQLCMD like e.g :i to read the file

declare @xx nvarchar(Max) =
:i MyScriptFileWithSomeInserts.sql

exec InsertDataProcedure [@ScriptContent] 

My setup

Visual Studio 2019 and target platform MS Azure SQL Database V12.

Update: How this was solved

This is what I ended up with after the help from John.

DECLARE @solutionDir VARCHAR(200),@File VARCHAR(200)
-- In the SQLCMD I have access to $(SolutionPath) so I need to do some 
-- cleaningto get the absolute path to the file
SELECT @solutionDir = REPLACE('$(SolutionPath)','MySolution.sln','');
SET @File = @solutionDir + 'Databases\MyDb\MyScriptFileWithSomeInserts.sql'
        
DECLARE @retvalue nvarchar(max)  
DECLARE @SQL nvarchar(max);
DECLARE @ParmDef nvarchar(50);

-- Read the content of the file
SELECT @SQL = N'SELECT @retvalOUT = BulkColumn FROM OPENROWSET(BULK ''' + @File + ''', SINGLE_BLOB) AS x' ;   
SET @ParmDef = N'@retvalOUT nvarchar(max) OUTPUT';
 
EXEC sp_executesql @SQL, @ParmDef, @retvalOUT=@retvalue OUTPUT;

-- And here I have all the content of the file.
PRINT '@retvalue: ' + @retvalue

Answer 1

Just a thought, but I didn't see OPENROWSET in your list

Declare @S varchar(max); 
Select @S = BulkColumn FROM  OPENROWSET(BULK 'C:\SomeDir\SomeScript.sql', SINGLE_BLOB) x; 


Print @S
--Exec(@S)