Using variables in SQL query

Question

I need to create a database but taking a name and path from variables:

declare @Datapath nvarchar = serverproperty('InstanceDefaultDataPath');

declare @Database nvarchar = 'DatabaseName';

use master
go

if exists (select name from sys.databases where name = '@Database')
drop database [@Database]
go

create database [@Database] 
on primary ( 
  name = '@Database_Data'),
  filename = concat(@Datapath, '@Database_Data.mdf'),
  size = 200MB,
  maxsize = 20GB,
  filegrowth = 40MB 
)
collate Latin1_General_CI_AI

go

But I get the error on first line:

Implicit conversion from data type sql_variant to nvarchar is not allowed. Use the CONVERT function to run this query.

And I am also getting an error on concat function.

I would like to use @Datapath and @Database on my query.

What am I missing?

UPDATE 1

declare @Datapath nvarchar(200) = cast(serverproperty('InstanceDefaultDataPath') as nvarchar(200));

declare @Database nvarchar(50) = 'DatabaseName';

use master
go

if exists (select name from sys.databases where name = '@Database')
drop database [@Database]
go

create database [@Database] 
on primary ( 
  name = '@Database',
  filename = concat(@Datapath, '@Database_Data.mdf'),
  size = 200MB,
  maxsize = 20GB,
  filegrowth = 40MB 
)
collate Latin1_General_CI_AI

go

Sébastien Sevrin · Accepted Answer · 2015-06-30 10:47:48Z

5

You have 2 mistakes in your T-SQL:

You don't specify the size of your @Datapath and @Database variables

Replace your

declare @YourVariable nvarchar = ...

By

declare @YourVariable nvarchar(<YourSize>) = ...

serverproperty returns a sql_variant so if you need a nvarchar, you can cast it.

Change

serverproperty('InstanceDefaultDataPath');

To

cast(serverproperty('InstanceDefaultDataPath') as nvarchar(200));

Here is the result:

declare @Datapath nvarchar(200) = cast(serverproperty('InstanceDefaultDataPath') as nvarchar(200));
declare @Database nvarchar(50) = 'DatabaseName';

Feel free to adjust the nvarchar size if needed.

answered Jun 30, 2015 at 10:47

Sébastien Sevrin

5,4132 gold badges25 silver badges42 bronze badges

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

7 Comments

Miguel Moura Over a year ago

But I still have a problem when using the variable in filename = concat(@Datapath, '@Database_Data.mdf'), or even with the convert option. Do you know how to solve this?

Sébastien Sevrin Over a year ago

Then you will have to post the error. Is the script just a sample or is it the real query?

Sébastien Sevrin Over a year ago

@Miguel - Do you want to name your database @Database or DatabaseName?

Miguel Moura Over a year ago

@Sébastian: I want its name to be DatabaseName. I just updated with the code I am trying now

Miguel Moura Over a year ago

@Sébastian: the code I am using, and that just posted, is the real query.

|

Collectives™ on Stack Overflow

Using variables in SQL query

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related