Getting string from variable

Question

DECLARE @dbfilepath nvarchar(128)
SET @dbfilepath = 'C:\SqlDataFiles\Cache.mdf'
GO

USE [master]
GO
CREATE DATABASE [Cache] ON  PRIMARY 
( NAME = N'Cache', FILENAME = @dbfilepath, SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
GO

Why doesn't this work?

It gives:

Msg 102, Level 15, State 1, Line 3 Incorrect syntax near '@dbfilepath'.

You cannot use a variable inside a CREATE DATABASE statement — marc_s
– marc_s, Commented Jun 14, 2011 at 15:48

Alex K. · Accepted Answer · 2011-06-14 15:53:41Z

1

You need to exec it;

USE [master]
GO
DECLARE @dbfilepath nvarchar(128) = 'C:\MSSQL\Cache.mdf'
DECLARE @SQL NVARCHAR(MAX) = N'CREATE DATABASE [Cache] ON PRIMARY (NAME = N''Cache'', FILENAME = ''' + @dbfilepath + ''', SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )'
EXEC(@SQL)

answered Jun 14, 2011 at 15:53

Alex K.

177k32 gold badges276 silver badges299 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Hasan Fahim · Accepted Answer · 2011-06-14 15:55:19Z

0

Try this:

DECLARE @dbfilepath nvarchar(128);
SET @dbfilepath = 'C:\\SqlDataFiles\\Cache.mdf';
PRINT @dbfilepath;

To use @dbfilepath in the Create Database statement, dynamic sql needs to be used.

edited Jun 14, 2011 at 15:55

answered Jun 14, 2011 at 15:44

Hasan Fahim

3,8751 gold badge34 silver badges51 bronze badges

2 Comments

Pankaj Over a year ago

It is printing the same path as mentioned.

Hasan Fahim Over a year ago

@SQL. Yes. But now the error won't come, which was mentioned in the question.

Collectives™ on Stack Overflow

Getting string from variable

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related