Passing a file path as a variable in SQL Server

Question

I'm trying to add a file to a file group in order to create a partition in SQL Server. When I pass in a hardcoded file path to the the filename, the code works. But when I use a variable for the file path, I get an error

Incorrect syntax near @FilePath or Unexpected symbol @FilePath

This is my code:

USE StudentRepository

BEGIN
    SET NOCOUNT ON;

    DECLARE @FilePath NVARCHAR(MAX) = "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLEXPRESS\MSSQL\DATA\",
            @FileName NVARCHAR(MAX) = "FirstTerm2020",
            @FileExt NVARCHAR(MAX) = ".NDF";
    DECLARE @count INT = 0;

    ALTER DATABASE StudentRepository   
    ADD FILE   
    (  
        NAME = 'FirstTerm2020',  
        FILENAME = @FilePath + @FileName + @FileExt,  --Error here!
        SIZE = 5MB,  
        MAXSIZE = 100MB,  
        FILEGROWTH = 5MB  
    )  
    TO FILEGROUP  FirstTerm2020
END

When I pass a hardcoded file path to the filename, it works and creates the required partitions. as below

--Code omitted for brevity
FILENAME =  "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLEXPRESS\MSSQL\DATA\FirstTerm2020.NDF"  --No error!

Please I need some help. How do I pass a file path as a variable?

Have you read the [ALTER DATABASE (Transact-SQL) File and Filegroup Options](https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-file-and-filegroup-options) documentation yet? None of the options accept values from variables, they're all literals. If you need to supply these paths dynamically then you will need to construct Dynamic SQL queries. — AlwaysLearning, Jan 21 '23 at 08:28
Check this https://learn.microsoft.com/en-us/sql/ssms/scripting/edit-sqlcmd-scripts-with-query-editor?view=sql-server-ver16 — Bogdan Sahlean, Jan 21 '23 at 13:48
double quotes are for object names in SQL, not string literals — Joel Coehoorn, Jan 22 '23 at 02:58
@BogdanSahlean - Please can you demonstrate your answer with an example. I don't understand how to use sqlcmd. Dynamic SQL does not work either, I have tried it. — Awar Denen, Jan 22 '23 at 03:22
@JoelCoehoorn- Single quotes are also not working that is why I decided to use double quotes instead. — Awar Denen, Jan 22 '23 at 03:53
@AwarDenen https://dba.stackexchange.com/questions/140721/howto-avoid-textdata-of-trace-gets-truncated — Bogdan Sahlean, Jan 22 '23 at 06:17

score 0 · Answer 1 · answered Jan 27 '23 at 20:32

This is how I solved the problem. I create a dynamic sql and passed the filepath variable like this.


USE StudentRepository

BEGIN
    SET NOCOUNT ON;

    DECLARE @FilePath NVARCHAR(MAX) = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLEXPRESS\MSSQL\DATA\',
            @FileName NVARCHAR(MAX) = 'SecondTerm2022',
            @FileExt NVARCHAR(MAX) = '.NDF';
    DECLARE @count INT = 0;
    DECLARE @sql NVARCHAR(MAX) = '
    ALTER DATABASE StudentRepository ADD FILEGROUP ' + @FileName + ';

    ALTER DATABASE StudentRepository   
    ADD FILE   
    (  
        NAME = '+ @FileName + ',  
        FILENAME = ''' + @FilePath + @FileName + @FileExt + ''',
        SIZE = 5MB,  
        MAXSIZE = 100MB,  
        FILEGROWTH = 5MB  
    )  
    TO FILEGROUP  '+ @FileName
END

EXEC sp_executesql @sql

My major problem was that I was ommitting the three single quotes near @FilePath

Passing a file path as a variable in SQL Server

1 Answers1